Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
quriouss
Contributor III

Aggregate functions to ignore/include drilldowns

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"



Tags (3)
2 Replies

Re: Aggregate functions to ignore/include drilldowns

Are you using a cyclic or drill down group? If yes, then you can use GetCurrentField(GroupName) to get which dimension you are on right now and use that in your aggregation function.

HTH

Best,

Sunny

quriouss
Contributor III

Re: Aggregate functions to ignore/include drilldowns

No I'm not - it's just a plain vanilla table which might expand (with more dimensions) in the future.

Community Browser