Changing metric aggregators

Follow

The default view for your metrics is SUM, but you have many other options. There are six aggregators available on all connections and three for only data sources stored on Déjà Vu. Changing your aggregator provides you with different ways to analyze your data.

To change your metric aggregator

  1. In the Query Builder, click the metric with the aggregator you would like to change.
  2. Select a new aggregator.

    The six aggregators available on all connections are:

    • AVG: The average (mean) of all results.
    • COUNT: The simple count of all results.
    • MIN: The smallest result.
    • MAX: The largest result.
    • DCOUNT: The number of different values (the distinct count).
    • SUMX: The row-by-row calculation of results. SUMX is usually used for calculated metrics or displaying the SUM of the same metric twice.

    The three aggregators available for only data sources stored on Déjà Vu are:

    • MED: The median of all results.
    • VARIANCE: The variance of all results.
    • STD_DEV: The standard deviation of all results.
  3. Click the check mark to save the new metric aggregator.

The COUNT and DCOUNT aggregators are particularly useful when dealing with numeric IDs or reference numbers. For example, you would probably never want to see the total (SUM) of invoice numbers or product SKUs. You can use the DCOUNT aggregator to total the number of results, rather than sum the actual values. 

Have more questions? Submit a request

4 Comments

  • 0

    Have you figured out how you get the COUNT to return 0? Mine comes back as Empty Set

  • 0

    Hey Eric,

    You could potentially write a conditional statement as a calculated attribute.

    Example:

    IF COUNT(measure) = NULL THEN "0" ELSE COUNT(measure) ENDIF;

    This should replace the null values with 0 or whatever you'd like. You could use "Null" for example. 

     

  • 0

    Median gets me 50th percentile, what if i want to get the 90th percentile?

  • 0

    I ended up handling all core data manipulation (like clearing out NULLs) in SQL before hooking the data source up to BIME; that works much better for me.

Please sign in to leave a comment.
Powered by Zendesk