Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Conditional Format with Hrank - Dynamic Range

Hi All,

I am currently working on building a P&L pivot table for multiple locations (the reason I am using Hrank). However, I am using slicers, so the maximum ranking is constantly changing based on my selections. For simplicity, lets assume that my selection gives me 8 locations. I am trying to highlight the top 3 a color and the bottom 3 a color, but am having a difficult time with creating a dynamic rank for the end ranking (i.e 😎

I know that this equation works:

If(HRank(total(sum(MS_FGS_Sales_Labor) -  sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

If(HRank(total(sum(MS_FGS_Sales_Labor) - sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) >5, LightRed(),))


But this equation does not:

If(HRank(total(sum(MS_FGS_Sales_Labor) -  sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

If(HRank(total(sum(MS_FGS_Sales_Labor) - sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) >(Count($(dim_StoreNumber)) - 3), LightRed(),))


I have tested the (Count($(dim_StoreNumber)) - 3) = 5 by plugging the information into a simple KPI. However, when I use it as a range, the equation highlights all of the values above the top 3. Can you provide explanation as to why the second equation does not work and how to correct it?

1 Solution

Accepted Solutions

Re: Conditional Format with Hrank - Dynamic Range

I might have accidentally removed a parenthesis, can you try again

If(HRank(total(sum(MS_FGS_Sales_Labor) -  sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

If(HRank(total(sum(MS_FGS_Sales_Labor) - sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) >(Count(TOTAL $(dim_StoreNumber)) - 3), LightRed(),))

View solution in original post

5 Replies

Re: Conditional Format with Hrank - Dynamic Range

How about this?

If(HRank(TOTAL (Sum(MS_FGS_Sales_Labor) -  Sum(MS_FGS_COGS_Labor)) / Sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

If(HRank(TOTAL (Sum(MS_FGS_Sales_Labor) - Sum(MS_FGS_COGS_Labor)) / Sum(MS_FGS_Sales_Labor)) >(Count(TOTAL $(dim_StoreNumber)) - 3), LightRed(),))

Not applicable

Re: Conditional Format with Hrank - Dynamic Range

Hi Sunny,

Unfortunately that erased the top 3 highlights and did not highlight anything red (all background colors remain unchanged).

Re: Conditional Format with Hrank - Dynamic Range

I might have accidentally removed a parenthesis, can you try again

If(HRank(total(sum(MS_FGS_Sales_Labor) -  sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) <=3, RGB(102,178,255),

If(HRank(total(sum(MS_FGS_Sales_Labor) - sum(MS_FGS_COGS_Labor)) / sum(MS_FGS_Sales_Labor)) >(Count(TOTAL $(dim_StoreNumber)) - 3), LightRed(),))

View solution in original post

Not applicable

Re: Conditional Format with Hrank - Dynamic Range

Perfect! Can you explain the reasoning of having to add TOTAL vs just having the count there?

FYI this worked perfectly

Re: Conditional Format with Hrank - Dynamic Range

From your response, it seemed that you had Location as your dimension and when you did just count(), you probably were getting 1 for each location.... by using Total, it showed 8 (sum of all location) across all location on the chart.... So 1-3 = -2, but you needed 8-3 for this to work