Changing the row sequence in pivot table.

Thu, 17 May 2012 06:53:58 GMT

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

Changing the row sequence in pivot table.

Jason_Michaelides — Thu, 17 May 2012 07:43:55 GMT

Can you not sort by your Rank() expression?

Changing the row sequence in pivot table.

Thu, 17 May 2012 10:36:54 GMT

Nopes...

topic Changing the row sequence in pivot table. in QlikView

Changing the row sequence in pivot table.

Changing the row sequence in pivot table.

Changing the row sequence in pivot table.