# Qlik Sense App Development

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 MVP

## 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(),))

5 Replies MVP

## Re: Conditional Format with Hrank - Dynamic Range

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). MVP

## 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(),)) 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 MVP

## 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