2 Replies Latest reply: Nov 29, 2017 8:00 AM by Dan Sullivan RSS

    Count product families between to aggr amounts

    Dan Sullivan

      I am trying to count the distinct combination of country&family where the rolling 4 weeks avg is less than the rolling 52 week avg.  My data is at a lower level than what I am trying to count so i have created variables to accommodate.

       

      v4WK_CountryFamily  --> this works properly and returns the correct AVG sales per day over the past 4 weeks at the aggregated country/family level

      AVG({$<SalesType=>}AGGR(Sum({$<[Rolling 4WK Flag]={1},SalesType=>}[USD Sales Amount]),[Ship Date],[Country Name],Family))

       

      v52WK_CountryFamily  --> this works properly and returns the correct AVG sales per day over the past 52 weeks at the aggregated country/family level

      AVG({$<SalesType=>}AGGR(Sum({$<[Rolling 52WK Flag]={1},SalesType=>}[USD Sales Amount]),[Ship Date],[Country Name],Family))

       

      Now I want to create a KPI that counts the number of CountryFamilies where the 4wk avg < 52wk avg....been trying something like this but it isn't giving the correct answer.  Any suggestions?

       

      Count(Distinct  {< [USD Sales Amount] = {"=$(v4WK_CountryFamily) < $(=v52WK_CountryFamily)"}>} "Country Name"&Family )