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.
robertrwb
New Contributor

Count function

Hello Everyone,

I need Your help for function Count that will show the number of Customers with positive deviation in sales between current year and last year. My expression for the calculation of this deviation is       

Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today()))"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])-Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today())-1)"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])

Robert

1 Solution

Accepted Solutions
Employee
Employee

Re: Count function

The nested aggregation might be a problem when adding the entire deviation aggregation inside the count.

It depends a bit where you want to aggregate the count. In a table column you can simply use a total function to find the count based on a if() statement. For a calculated total I would suggest looking at a Aggr() based expression. See attached QVF for example on the principle.

With the assumption that your dimensions is CustomerID, the expression could be something like:

=Count( Aggr( if( Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today()))"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])-Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today())-1)"},Month={"$(=Month(Today())-1)"}>}[Volume sqm]) >0,1), CustomerID))

3 Replies
balrajahlawat
Esteemed Contributor

Re: Count function

may be like this?

=count(distinct if(DeviationExpression>0, CustomerID)

Employee
Employee

Re: Count function

The nested aggregation might be a problem when adding the entire deviation aggregation inside the count.

It depends a bit where you want to aggregate the count. In a table column you can simply use a total function to find the count based on a if() statement. For a calculated total I would suggest looking at a Aggr() based expression. See attached QVF for example on the principle.

With the assumption that your dimensions is CustomerID, the expression could be something like:

=Count( Aggr( if( Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today()))"},Month={"$(=Month(Today())-1)"}>}[Volume sqm])-Sum({$<[Product Line]-={'RSC'},Year={"$(=Year(Today())-1)"},Month={"$(=Month(Today())-1)"}>}[Volume sqm]) >0,1), CustomerID))

robertrwb
New Contributor

Re: Count function

Toni,

thanks a lot! It's works

Robert