topic Re: problem with rank accumulations and sorting in QlikView

problem with rank accumulations and sorting

wizardo — Mon, 08 Feb 2016 12:26:39 GMT

Hi,

(i have this problem in my model but i was able to reproduce it with the generic NORTHWIND database

i'v attached both a demo QVW and the Northwind database in an xls file)

i have a striehgt table with 3 dimensions

Category, Country, Company

i have 3 expressions

Count(DISTINCT OrderID) // counts the number of orders

num(rank(Count(DISTINCT OrderID) ,4)) // ranks the above expression

Count(DISTINCT OrderID) / Count( TOTAL <CategoryName,Country> DISTINCT OrderID) // gives the % for each company from the total for each Category and Country

now i want to accumulate the 3rd experssion so for each Category and Country it will start again

so i took the 3 expression and used rangesum and above functions + the rowno function like this

(taken from HIC blog post about accumulations)

the expression looks like that:

RangeSum(Above(

Count(DISTINCT OrderID)

Count( TOTAL <CategoryName,Country> DISTINCT OrderID)

,0,RowNo()))

i want the sort order to be

Category (text sort) and then

Country (text sort)

and for each country the Company should be sorted by the rank in asc order (or the 1st expr desc)

so i will get the correct accumulation

but no matter what i try the sort order does not get right

(the accumulation works but since the sort order is wrong its of no use )

any ideas why?

Re: problem with rank accumulations and sorting

sunny_talwar — Mon, 08 Feb 2016 12:31:09 GMT

Because the Above function is Sort dependent, as soon as you change the sorting, the number will change. The way around this is to sort the data correctly in the script and use Aggr() function because Aggr() uses sort order and not the order specified in the chart.

Re: problem with rank accumulations and sorting

jonathandienst — Mon, 08 Feb 2016 12:37:01 GMT

With multiple dimensions, you may need a TOTAL inside the Above as well, otherwise it is referring to the first dimension only

RangeSum(Above(TOTAL

Count(DISTINCT OrderID)

Count( TOTAL <CategoryName,Country> DISTINCT OrderID)

,0,RowNo()))

Re: problem with rank accumulations and sorting

jonathandienst — Mon, 08 Feb 2016 12:38:30 GMT

And Rank has the same issue. You may need Rank(TOTAL ....)

Re: problem with rank accumulations and sorting

wizardo — Mon, 08 Feb 2016 13:37:57 GMT

Hi jontydkpi

Thanks for replying.

as i said in my post the accumulation works.

but since the chart is not sorted the way i want it acculunlate the wrong things and thats my problem

why is the sort order not working

Re: problem with rank accumulations and sorting

wizardo — Mon, 08 Feb 2016 13:40:40 GMT

Hi,

the accumulations works and accumulates the number correctly and starts again on every combination of category and country just as i need. if i will put the TOTAL it will accumulate everything and wont restart for each combination of category and country.

like i said

my problem is that the sort is not working whice leads to the accumulation working but not in the order i need

Daniel