2 Replies Latest reply: Aug 20, 2015 6:23 AM by Simon Hogg RSS

    Aggregate functions to ignore/include drilldowns

    Simon Hogg

      I think I have got the hang of counting distinct clients by using the AGGR function;


      COUNT ( Distinct ( if ( AGGR ( Sum (A+B+C) , Client ) > 0 , Client )

      But this doesn't do quite what I want.  For example, if I put this measure into a table, split by year, it will only give me one count per client (each client will be counted either in 2015 or in 2014 or in 2013, etc. If a client is in both 2014 and 2015, they will only be counted in either one or the other, not both).

      What I want to do is, for each year (and department, and item-type, etc.) count each individual client.

      I can solve this by adding to the aggregation function;

      COUNT ( Distinct ( if ( AGGR ( Sum (A+B+C) , Client, Department, Item_Type ) > 0 , Client )

      But I have lots of different dimensions.  Do I really have to type out a long list of all possible dimensions or can I use some sort of Set-Analysis / Dollar-Expansion to say, "Count the Clients, and just include whatever dimensions are in the table/chart/etc"