topic Accumulating values on each time range in QlikView

Accumulating values on each time range

Mon, 01 Feb 2010 15:00:01 GMT

Hello!

Well i think this should be easy but the worse thing is that i can not solve it yet.

I have a table containing an order list and time span. I am having a chart viewing the value for each month which is working fine. Now i want to create a chart to list the accumulated value from the previous month/months on each month. That is to say jan will show only jan values but feb with show the sum of values from jan and feb, and so on at last dec will show the sum of the values from jan to dec.

How can i achieve this in the easiest way ?

Accumulating values on each time range

Mon, 01 Feb 2010 15:36:12 GMT

Hi,

In your graph properties and in the Expression tab , try to check the full accumulation (bottom left ).

Philippe

Accumulating values on each time range

Mon, 01 Feb 2010 15:37:06 GMT

Hi,

try to check "Full Accumulation" in Expression tab.

I hope this will work 🙂

Milda

Accumulating values on each time range

Mon, 01 Feb 2010 15:51:07 GMT

Thanks for your answer.

Sorry but i already tried that and it does not work. Full accumulation accumulates the values of the expression (y-axis) and does not help my % expression (X/Y*100) as i need to accumulate both the X values and the Y values before dividing other wise it will be wrong in the % numbers. Below is one of my expressions that is working for values for each month only. I want to modify this to use it for accumulated values in the expression:

= Count(distinct if(type = 'X', wo)) / Count(distinct wo)) * 100

Accumulating values on each time range

Mon, 01 Feb 2010 15:55:32 GMT

May be i did not explain my problem correctly from the beggining.

I need to accumulate the value in my expression before deviding and not after.

Accumulating values on each time range

Mon, 01 Feb 2010 16:40:36 GMT

Do you have an exemple of your application ?

You can probably do what you want to achieve using the Set Analysis Syntax.

To help you , we need more information . The application could be a good start.

Philippe

Accumulating values on each time range

johnw — Mon, 01 Feb 2010 21:45:22 GMT

Here's one way to accumulate data. If it's conditional, like a YTD accumulation for multiple years, you can add an if() statement around the above():

rangesum(above(ExpressionName),Expression)

So for your expression, I think you'd want this:

rangesum(above(FirstExpressionName) ,count({<type={'X'}>} distinct wo))
/ rangesum(above(SecondExpressionName),count( distinct wo))

And then display it as a percentage using the Number tab instead of multiplying by 100. I used set analysis for the type='X' check because it's usually faster, not because it's necessary here.

Accumulating values on each time range

Mon, 01 Feb 2010 22:04:14 GMT

Hello Philippe and all !

here comes an example file.

Accumulating values on each time range

johnw — Mon, 01 Feb 2010 22:23:05 GMT

OK, here's what I think you're asking for, using basically the technique above.

Accumulating values on each time range

Mon, 01 Feb 2010 22:34:02 GMT

Thanks alot it really works as i wanted

Accumulating values on each time range

Fri, 19 Nov 2010 16:39:54 GMT

Hi, I have the same problem

I have an expression as follows: its name is "CASH FLOW"

sum ( {<class = {a}>} [Balance] / sum ( {<{class = {b}>} [Balance])

when I accumulate = it accumulates the ratios - which is not what I want

I want - "cumulative" sum ( {<class = {a}>} [Balance] / "cumulative" sum ( {<{class = {b}>} [Balance])

The dimension is period i.e. Year 1, 2, 3 etc

Accumulating values on each time range

johnw — Fri, 19 Nov 2010 19:40:39 GMT

Rangesum(above()) should still work. Create separate hidden expressions for the class a and class b balances, then rangesum each over the current row and all above rows. Probably something like this:

Class A Balance = sum({<class={'a'}>} Balance) // hidden expression
Class B Balance = sum({<class={'b'}>} Balance) // hidden expression
Cash Flow = rangesum(above("Class A Balance"),0,rowno())
/rangesum(above("Class B Balance"),0,rowno())

A better approach might be to handle it with data, though. Let's say your first year was 2001:

AsOfYear, Year
2001, 2001
2002, 2001
2002, 2002
2003, 2001
2003, 2002
2003, 2003
etc.

Use AsOfYear in the chart, and the sums will be a full accumulation (don't set the full accumulation option), and will work when dividing the sums.