# 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.
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

## 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
Esteemed Contributor

## Re: Count function

may be like this?

=count(distinct if(DeviationExpression>0, CustomerID) 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))

New Contributor

## Re: Count function

Toni,

thanks a lot! It's works

Robert

Community Browser