Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wizardo
Creator III
Creator III

problem with rank accumulations and sorting

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?

table.png

5 Replies
sunny_talwar

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.

jonathandienst
Partner - Champion III
Partner - Champion III

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()))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
wizardo
Creator III
Creator III
Author

Hi

wizardo
Creator III
Creator III
Author

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