How to sort/order accumulation of aggr(rangesum(above(sum(X),0,rowno())),Y) in calculated dimension?

[#1]

Hi, anyone knows which formula to use if to sort this calculated dimension :

=aggr(rangesum(above(sum(Sales),0,rowno())),Customer) ??

Above will accumulate Sales figure but purely based on loading sort order. While I need to accumulate the Sales figure based on highest Sales in descending order so that I can project on the Top10 or next Top10 or so. Currently I got a workaround using macro to rank and accumulate which is performing awfully slow in bigger dataset.

Workaround:

My macro :

for rank = 1 to vTotalCountCustomer

vSales = CDbl(ActiveDocument.Evaluate("sum({<Customer={'=rank(sum(Sales),Customer)="& rank &"'}>} Sales )"))

.. assign rank to var (which passed Top10 benchmark, next Top10 and so on..)

next

OR

for RowIter = 1 to table.GetRowCount-1 ...assuming I have straight table accumulated nicely

vSales = CDbl(table.GetCell(RowIter,2).Text)

.. assign rank to var (which passed Top10 benchmark, next Top10 and so on..)

next

My chart :

Calc Dimension:

=if(aggr(sum({<Customer={"=rank(sum(Sales))<=$(vRank1)"}>} Sales),Customer), 'D-01',

if(aggr(sum({<Customer={"=rank(sum(Sales))>$(vRank1) and rank(sum(Sales))<=$(vRank2)"}>} Sales),Customer),'D-02','D-03')) ......these are nested IF till D-10 to be exact.

Expression:

=sum(Sales)

Appreciate if any expert could suggest, thx.

Davis

[#2]

Hi,

Check the attached application.

I have used below formulae to sort the dimension.

=

aggr(rank(aggr(sum(Sales),Product),0,1),Product)

Hope this may help you.

-Sridhar

[#3]

Hi Sridhar,

I'm afraid it does not help. I suspect your method only sorts the end result while my problem need to be sorted at front-end calculated dimension.

My calculated dimension is :

=if(aggr(rangesum(above(sum(APE),0,rowno())),Name)<= vDecileTop1,'D-1',

if(aggr(rangesum(above(sum(APE),0,rowno())),Name)> vDecileTop1 and aggr(rangesum(above(sum(APE),0,rowno())),Name)<= vDecileTop2,'D-2',

if(aggr(rangesum(above(sum(APE),0,rowno())),Name)> vDecileTop2 and aggr(rangesum(above(sum(APE),0,rowno())),Name)<= vDecileTop3,'D-3',

if(aggr(rangesum(above(sum(APE),0,rowno())),Name)> vDecileTop3 and aggr(rangesum(above(sum(APE),0,rowno())),Name)<= vDecileTop4,'D-4'.......so on

It accumulates based on the ordering when raw data is loaded into QVW which is not what I wanted.

[lzanetti]

Hi Davis,

in my (little) experience, if you put RangeSum in a plot, ordering won't work.

I solved in this way:

1) in the order section put the dimension first

2) Order by Formula, then put in the formula Sum(Sales), Decreasing order.

This should work.

Best,

Lou Igi