5 Replies Latest reply: Feb 8, 2016 8:40 AM by DANIEL CHOTZEN RSS

    problem with rank accumulations and sorting

    DANIEL CHOTZEN

      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