Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik and ServiceNow Partner to Bring Trusted Enterprise Context into AI-Powered Workflows. Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
Primrose
Contributor III
Contributor III

Ranking with Aggr() per region

Hello,

I'm working on an app with a complicated data model. I have two expressions. The first one displays the ranking of products based on number of orders. The second one displays ranking based on customer's rating.

The first one is something like this:
Aggr(Rank(
if(Count(distinct {<Period={$(vCurrent_Period)},FieldType={'Quantity'},flag={1}>} [Counter Field])>=vThreshold,
Sum({<flag={1}>}Aggr(Sum({<Period={$(vCurrent_Period)},FieldType={'Quantity'}, flag={1}>}ValueField)*if(vType='Quantity',
sum({<flag={1}>}[Regional sales Weight])
,1
),
[Counter Field],ProductId)
)
),4,1),
Region,ProductId)

The second one is:
Aggr(Rank(
if(Count(distinct{<Period={$(vCurrent_Period)},FieldType={'Quality'},Rating={">0"}>} [Counter Field])>=vValue_Threshold,
(Count({<Period={$(vCurrent_Period)},FieldType={'Quality'},Rating={4,5}>}Rating) /
Count({<Period={$(vCurrent_Period)},FieldType={'Quality'},Rating={">0"}>}Rating))
-
(Count({<Period={$(vCurrent_Period)},FieldType={'Quality'},Rating={1,2}>}Rating) /
Count({<Period={$(vCurrent_Period)},FieldType={'Quality'},Rating={">0"}>}Rating))
)
,4,1)
,ProductId)


My goal is to make a sheet, where user can see stats per selected product ID. The first expression was reworked to look like this:

max({<ProductId = {Id1}>} Aggr(Rank(
if(Count(distinct {<Period={$(vCurrent_Period)},FieldType={'Quantity'},flag={1}>} [Counter Field])>=vThreshold,
Sum({<flag={1}>}Aggr(Sum({<Period={$(vCurrent_Period)},FieldType={'Quantity'},
flag={1}>}ValueField)*if(vType='Quantity',
sum({<flag={1}>}[Regional sales Weight])
,1
),
[Counter Field],ProductId)
)
),4,1),
Region,ProductId))

It works fine and I can see the correct ranking per region for the selected product ID. However, if I try to rework the second expression using the same approach, it doesn't work. Do you have any ideas why?

 

Thanks!

0 Replies