Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
may be like this?
=count(distinct if(DeviationExpression>0, CustomerID)
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))
Toni,
thanks a lot! It's works
Robert