topic Re: Accumulative sum sort-by-dimension problem in QlikView

Accumulative sum sort-by-dimension problem

Sun, 19 May 2013 13:34:20 GMT

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.

Re: Accumulative sum sort-by-dimension problem

swuehl — Sun, 19 May 2013 14:36:41 GMT

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

Re: Accumulative sum sort-by-dimension problem

Sun, 19 May 2013 16:25:13 GMT

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

Re: Accumulative sum sort-by-dimension problem

swuehl — Sun, 19 May 2013 17:23:34 GMT

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)

Re: Accumulative sum sort-by-dimension problem

Sun, 19 May 2013 17:57:53 GMT

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.

Re: Accumulative sum sort-by-dimension problem

Sun, 19 May 2013 18:15:46 GMT

Thank you, Stefan.

Exactly what my app need it.