Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please consider following scenario:-
I have 1000 rows of Customer names in dimension table and I have taken top 50 customer names and order amount (field in fact table) sorted on the basis of their order amount in one pivot table. I have calculated top 50 order amount total in the same pivot table using rank and aggregate functions .
Following is the expression:-
if(RowNo()>0,sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}ORDR_AMT),
sum(if(aggr(rank(sum(ORDR_AMT),4),CUST_NAME)<51,aggr(sum({$<QTR_NBR={1},YEAR_CD={'P'},TREND_CAT_CD={'HI'}>}ORDR_AMT),CUST_NAME))))
and following is the dimension :
= aggr(if(rank(sum({$<QTR_NBR={4},YEAR_CD={'C'},TREND_CAT_CD={'HI'}>}ALWD_AMT),4)<51,CUST_NAME),CUST_NAME)
I have also calculated All others total (1000-50 = 950 total) using partial sum feature of the pivot table. This is giving me a pivot table having rows:-
Top 50 customer names (50 rows)
All others total
Top50 Total
while our client wants it in following way:-
Top 50 customer names (50 rows)
Top50 Total
All others total
I am not able to swap last two total rows in any way in my pivot. Can you please help me out.
Thanks,
Kirti
Can you not sort by your Rank() expression?
Nopes...