Client needs a heatmap. Legacy dashboard shows only top 3 clients grouped by business unit with top 3 determined by total revenue. However, Client wants to see ALL the revenues of the 3 clients and not just the row containing the revenue that is the top 3. Illustratively, imagine the if the top 3 clients by revenue for the year is Coke, Toyota and Microsoft. Using q=limit q 3 will only show the 3 rows each showing the revenue fo rthe 3 BU's. ex: IBM=$10M, Coke=$9M, Toyota=$7m. Cleints wants to see all the revenues of the 3 and not just the $10m, $9m, $7m . This is accomplisehd by using rank() and partition. It flags the 3 companies and labels them by validAcct and then all the rows that has 'validAcct' is displayed.

WITH BINDINGS
q= load "x_LS_Invoice_Volume";
q1 = group q by ('{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}');
q1 = foreach q1 generate '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}','{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}', coalesce(sum('Revenue'),0) as 'Revenue',rank() over([..] partition by all order by sum('Revenue') desc) as 'rank',case when (rank() over([..] partition by all order by sum('Revenue') desc)) < 11 then  1 else 0 end as 'validAcct';
q2=group q1 by ('{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}','validAcct');
q2 = foreach q2 generate '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}','validAcct'as 'validAcct',sum('Revenue') as 'rev', max('validAcct') over([..] partition by '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}') as 'inSet';
res = filter q2 by 'inSet' == 1;
res= foreach res generate '{{column(q_WS_Rows_1.selection,["Valu"]).asObject()}}', '{{column(q_WS_columns_1.selection,["Valu"]).asObject()}}','rev' as 'revenue',case when 'rev' >= {{column(static_1.selection,["Valu"]).asObject()}} then "Above Threshold" else "Below Threshold" end as 'Threshold';
res= order res by 'revenue' desc;

BINDINGS RESOLVED

q= load "0Fb1T000000ob5mSAA/0Fc1T000005jHg4SAE";
q = filter q by date('Date_Year', 'Date_Month', 'Date_Day') in ["current week".."current day"];
q1 = group q by ('Account.Owner.Name', 'LOB2');
q1 = foreach q1 generate 'Account.Owner.Name','LOB2', coalesce(sum('Revenue'),0) as 'Revenue',rank() over([..] partition by all order by sum('Revenue') desc) as 'rank',case when (rank() over([..] partition by all order by sum('Revenue') desc)) < 11 then  1 else 0 end as 'validAcct';
q2=group q1 by ('Account.Owner.Name', 'LOB2','validAcct');
q2 = foreach q2 generate 'Account.Owner.Name', 'LOB2','validAcct'as 'validAcct',sum('Revenue') as 'rev', max('validAcct') over([..] partition by 'Account.Owner.Name') as 'inSet';
res = filter q2 by 'inSet' == 1;
res= foreach res generate 'Account.Owner.Name', 'LOB2','rev' as 'revenue',case when 'rev' >= 5000 then "Above Threshold" else "Below Threshold" end as 'Threshold';
res= order res by 'revenue' desc;
​