Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dan_sullivan
Contributor II

Count product families between to aggr amounts

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 )

2 Replies
omarbensalem
Esteemed Contributor

Re: Count product families between to aggr amounts

maybe use if condition?

If ( aggr( $(v4WK_CountryFamily) < $(v52WK_CountryFamily), dimension) , count( distinct family), null())

dan_sullivan
Contributor II

Re: Count product families between to aggr amounts

no luck.  doesn't return any value.  tried a few variations of that methodology.