how to highlight Top 3 Hour Sales by Region in PIVOT Table?

[jim_chan]

Hi all, 

I have a PIVOT adhoc table, its Sales by Region by Hour. now, user request, when i select dimension - Region, i can see Region A, top 3  sales by hour are:  06:00 - $1000, 14:00 - $5000, 18:00 - $15000, so these 3 values will be highlighted. 

But i got it wrong. seems like my script  is showing me the TOP 3 Region which has the Top 3 sales by EVERY hour. 

My expected result is: Region A, top 3  sales by hour are:  06:00 - $1000, 14:00 - $5000, 18:00 - $15000, so these 3 values will be highlighted.

I have 2 dimensions - Region & Hour. 

variable: vP1AvgSales

background expression: If(Rank(Total Count(vP1AvgSales))<4,RGB (255,0,0))   - which i think is wrong.

Please help guys!

Rgds

Jim

[vinieme12]

Try

=Aggr(if(Rank(Sum(Sales),4,1)<=3,red()),Hour,Region)

[jim_chan]

I have tried with the script given .  but turned out not the result i was expecting.  this script highlighted  each hours, top 3 sales region.

but my expected result is: Region A, top 3  sales by hour are:  06:00 - $1000, 14:00 - $5000, 18:00 - $15000, so these 3 values will be highlighted.

[vinieme12]

try below

=if(HRANK(sum(Sales),4,1)<=3,red())

or

=Aggr(if(Rank(Sum(Sales),4,1)<=3,red()),Region,Hour)

[jim_chan]

i am sorry Vinneth, i have already tried with both. but still the same result. not as expected. 

Anyway, is there a way not to highlight the Totals? 

Rgds

Jim

[vinieme12]

You can use 

If(rowno()<>0, if(your rank ❤️ expression,then color) )

[jim_chan]

HI Vineeth, 

tried with that too . but still the same. I have added this script, in the Region properties - Background expression.. 

If(rowno()<>0,

Aggr(if(Rank(COUNT(vAvgSales),4,1)<=3,white()),HOUR,Region))

Rgds

Jim

[vinieme12]

using HRANK( )

=If(rowno()<>0,    // IF NOT TOTAL ROW

if(HRank(COUNT(vAvgSales),4,1)<=3,  RED() )   //  THEN ONLY COLOR Dimension rows 

) //END IF

OR AGGR()

If(rowno()<>0,

if(Aggr(Rank(COUNT(vAvgSales),4,1),HOUR,Region)<=3, RED() ) )