Accumulative sum sort-by-dimension problem

[#1]

Hi all,

I've created an accumulated field as shown in the pic 1 to show the %-of-accumulation along the months. Expression is on pic 2. It goes well.

What I'd like to show is, when a month is selected it'll show the progress/accumulated percentage as in the table shown. However, when i click (sort) by month (dimension), it's not showing the accumulated percentage, but only the division data per month, which make it look like over-budget. See pic 3. I'd like to, when i clicked 'DESEMBER' or month 12, it shows 99.94% instead of 111.16%.

I've tried aggregation by dimension, rowno(TOTAL), as suggested here and here the result is the same, it can accumulated well in field, but showing not-accumulated-result when sort by dimension. Any idea, please? Thanks for your all concern.

[swuehl]

It might be possible to do what your require like discussed here

Take care that the aggr() dimension values have same load order as the sort order you require in your table (Your month load order should be chronological).

If you can post a small sample, this will help us to help you with the syntax of the expression.

Hope this helps,

Stefan

[#3]

Hi Stefan,

Thanks for your reply, and yes it was the same problem i'm facing.

I created sample and tried to put your sugested expressions (see the sample), yet it still didn't show the correct %-of accumulation, both sorted by dimension(s) or not.

The expression(s):

1. my prev expression show correct % if not sorted by dimension(s)

=rangesum( above( Sum(actual),0,12) ) /rangesum( above( Sum(budget),0,12) )

2. this expression show the same result as no. 1 if sorted by month (111.

=aggr(rangesum(above(total sum({1} actual)

/ sum({1}budget),0,RowNo(TOTAL))),product,month)

/*

=aggr(rangesum(above(total sum({1} actual)

/ sum({1}budget),0,RowNo(TOTAL))),month,product)

*/

3. the other expressions not showing the result (NULL).

[swuehl · Accepted solution]

Maybe like attached?

Changes 2 things:

- Load Order of month chronological

- expression to

=aggr(

rangesum( above( Sum({<month= >} actual),0,12) )

/ rangesum( above( Sum({<month= >} budget),0,12) )

,product, month)

[#5]

Hi again Stefan,

I forgot to mention that in my load order, it already sort/order chronologically by month 1-12, per product, as I copied in the LOAD * INLINE on sample. Thanks in advance.

[#6]

Thank you, Stefan.

Exactly what my app need it.