Changing the row sequence in pivot table.

[#1]

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

[Jason_Michaelides]

Can you not sort by your Rank() expression?

[#3]

Nopes...