Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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()))
And Rank has the same issue. You may need Rank(TOTAL ....)
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
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