topic Re: Sorting Order in QlikView

Sorting Order

upaliwije — Thu, 24 May 2018 10:30:14 GMT

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.

Re: Sorting Order

sunny_talwar — Thu, 24 May 2018 11:33:39 GMT

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)

Re: Sorting Order

upaliwije — Thu, 24 May 2018 16:07:44 GMT

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.

Re: Sorting Order

sunny_talwar — Thu, 24 May 2018 16:11:30 GMT

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?

Re: Sorting Order

upaliwije — Thu, 24 May 2018 16:26:02 GMT

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 ?

Re: Sorting Order

sunny_talwar — Thu, 24 May 2018 16:32:54 GMT

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

Re: Sorting Order

upaliwije — Thu, 24 May 2018 16:37:50 GMT

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 ?

Re: Sorting Order

sunny_talwar — Thu, 24 May 2018 16:40:56 GMT

coupled? Not sure I understand....

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

Re: Sorting Order

upaliwije — Thu, 24 May 2018 16:47:59 GMT

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

Re: Sorting Order

sunny_talwar — Thu, 24 May 2018 16:48:43 GMT

okay

Re: Sorting Order

upaliwije — Thu, 24 May 2018 17:09:04 GMT

This is my real data. Pls look into this and help me

Re: Sorting Order

sunny_talwar — Thu, 24 May 2018 17:27:34 GMT

Is this what you want? No more nulls?

Re: Sorting Order

upaliwije — Fri, 25 May 2018 03:34:56 GMT

Thanks Sunny,

This is not excatly I want. Whne you view the pivot table without selecting month there no null values. all values are associated with a Branch. Only when I select amonth the problem comes. What I want is to not to have any null value when I select a month without supressing Zero Values. Pls help

Re: Sorting Order

sunny_talwar — Fri, 25 May 2018 11:22:26 GMT

Try this

=Aggr(Dual(Only({<MONTH, YEAR>}BCOD),

RangeSum(

Rank((SUM({<YEAR={"$(=MAX([YEAR])-1)"}, MONTH>}PREMIUM)

-SUM({<YEAR={"$(=MAX([YEAR])-2)"}, MONTH>}PREMIUM))

/SUM({<YEAR={"$(=MAX([YEAR])-2)"}, MONTH>}PREMIUM)*100),

Rank(SUM({<YEAR={"$(=MAX([YEAR])-1)"}, MONTH>}PREMIUM))/1E7,

Only({<MONTH, YEAR>}Aggr(NODISTINCT Rank(Only({<MONTH, YEAR>}CLASS)), CLASS)) *1000000)

), CLASS, BCOD)

Re: Sorting Order

upaliwije — Sun, 27 May 2018 02:31:41 GMT

Thanks Sunny.

It is perfect and works fine though It is difficult for me to undestand.