3 Replies Latest reply: Aug 29, 2016 4:30 AM by robertrwb robertrwb

# 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

• ###### Re: Count function

may be like this?

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

• ###### 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))

• ###### Re: Count function

Toni,

thanks a lot! It's works

Robert