Sorting Order

[upaliwije]

Hi Friends

I have created a pivot table in my QV document which is attached herewit. In my pivot tbale I want to sort Dept based on growth rate of 2017. However though Department are sorted in correct way against Dept 'A' other two dept take a different order. Kidnly help me to sort out this issue. My correct table should look like shown below.

[sunny_talwar]

Is this what you wanted

Use the following calculated dimension instead of using DEPT as your dimension

=Aggr(Dual(DEPT,

Rank((SUM({<YEAR={"$(=MAX([YEAR]))"}>}AMOUNT)

-SUM({<YEAR={"$(=MAX([YEAR])-1)"}>}AMOUNT))

/SUM({<YEAR={"$(=MAX([YEAR])-1)"}>}AMOUNT)*100)

+

Rank(SUM({<YEAR={"$(=MAX([YEAR]))"}>}AMOUNT))/1E10

+

Aggr(NODISTINCT Rank(CLASS), CLASS) *10000), CLASS, DEPT)

[upaliwije]

Thanks Sunny,

It works . But I do not understand division by /1E10. Pls explain. The other issue is I have more than 10 DEPT in my real data.

[sunny_talwar]

1) 1E10 = 10,000,000,000 -> I am dividing Rank(SUM({<YEAR={"$(=MAX([YEAR]))"}>}AMOUNT)) by that number

2) You have 10 DEPT? So, what is the problem with that?

[upaliwije]

Thanks Sunny

But in my real data I get row od data without a branch as shown below

That Last row has no branch . What could be the reason Sunny please ?

[sunny_talwar]

I am not sure... is that a needed row or do you not want to see it?

[upaliwije]

That is not needed . when I select a month in my list box I get this blank row. In fact my expression is coupled with month in my real data.

Sum( {$<YEAR ={"$(=MAX([YEAR]))"},MONTH={"<=$(=max(MONTH))"}>}[PREMIUM] )

Is it due to that ?

[sunny_talwar]

coupled? Not sure I understand....

In order to hide the null row, just select 'Suppress When Value is Null' for the second dimensions

[upaliwije]

No Friend I will send sample of my real data. So that you can have a look please

[sunny_talwar]

okay