topic Accumulating sorted values in QlikView

Accumulating sorted values

Wed, 02 Sep 2009 03:31:48 GMT

Hello all,

I have a chart that has two dimensions - Product Group and Item and some expressions (sales, cost, etc) that are the sum of a selected date range. Then I have two other expressions that sum the current value for the item based on the date entered regardless of the date range entered for the sales data:

sum({$<month_end_date=>} if(current_value_date = CurValDate, current_value_at_cost))

I am sorting this expression (Current Value) in descending value within Product Group. Then I have the same expression that I want to use to have a running accumulation total of the current value (Accum Value). My chart should look like this:

Product Group Item Sales Cost GM$ Current Value Accum Value

ABC 12345 $100 $80 $20 $500 $500

ABC 33445 $300 $200 $100 $350 $850

ABC 56789 $50 $30 $20 $100 $950

Etc.

The problem is when I mark the Accum Value expression for Full Accumulation it doesn't accumulate a running total, it only gives me the value of that one line in the chart. I tried using a rangesum(above()) function but couldn't get it to work either. Of course I don't quite understand that function entirely.

Thanks in advance.

Accumulating sorted values

Oleg_Troyansky — Wed, 02 Sep 2009 04:36:09 GMT

would be great if you could post an example...

If I understand your description correctly, you are trying to use both "Full Accumulation" feature and the function above()... In order to calculate a running total, you need to use either one or another...

1. Use a "regular" expression, and check "Full Aggregation" on the Expressions Properties. If for some reason accumulation does not produce expected results (maybe something about your Set Analysis expression?), you can calculate accumulated value by yourself, using function above() - something along the lines of:

if ( rowno() = 1, Column(3), Column(3) + above(column(4)))

, where column 3 is the expression and column 4 is the same expression only accumulated. Accumulation happens due to the use of function above() that returns the previous valye of the running total.

cheers!

Oleg

Accumulating sorted values

Wed, 02 Sep 2009 20:16:30 GMT

Hi Oleg,

I actually am not trying to do both "Full Accumulation" and the above() function together. I was going between the two to see if either would give me the desired result. I did get the above() function to work thanks to your suggestion however it changes my sort order for the "Current Value" column (the column I'm accumulating). I would like that column sorted in descending order and the accumulation column a running total of those sorted values. It seems I can get the values to sort in descending order when I use Full Accumulation, however the accumulation doesn't work. Or I can use the above() function to get the accumulation to work but the sorting is wrong. Not sure why I can't get the two to work together.

I attached two examples, one with Full Acuumulation (not working) and it sorting correctly (called "full_accum") and the other with the accumulation working using the above() function but with it sorting wrong (called "above_accum").

Thanks again.

Accumulating sorted values

Wed, 02 Sep 2009 20:18:06 GMT

Here's the other example. For some reason I can't figure out how to upload more than one file per reply???

Thanks.

Accumulating sorted values

Oleg_Troyansky — Wed, 02 Sep 2009 23:10:52 GMT

Hi!

I looked at both of your examples, and it looks like there is a bug, or maybe even a couple of them:

- the "Full Accumulation" works for a single dimension (Item), or for two dimensions if Item is the first. As soon as the Group becomes the first dimension, the full accumulation stops working

- adding the expression that's using "above(), is messing up the sort order - looks like another bug.

I'd strongly recommend to send both of your sample to QlikTech as a bug report.

thanks!

Oleg

Accumulating sorted values

Thu, 03 Sep 2009 02:03:14 GMT

Thanks. This may seem like a simple question but how do I go about reporting this as a bug to QlikTech?