topic Re: Progressive SUM, selection indipendent in QlikView

Progressive SUM, selection indipendent

Anonymous — Mon, 09 Nov 2015 14:11:05 GMT

Hi,

i have this table ( black font. I need to calcolate the RED one):

YEAR MONTH MONEY PROGRESSIVE

2011 1 100 100

2011 2 200 300

2011 3 200 500

2011 4 400 900

2011 5 300 1200

2011 6 200 1400

2011 7 800 2200

..... ..... ......

2015 1 200 2200 + .... + 200

2015 2 300 2200 + .... + 200 + 300

2015 3 200 2200 + .... + 200 + 300 + 200

2015 4 800 2200 + .... + 200 + 300 + 200 + 800

...... ...... .......

I create a chart with:

- DIMENSION : MakeDate(YEAR,MONTH)

- EXPRESSION: MONEY

If in EXPRESSION I set "FULL ACCUMULATION" i obtain PROGRESSIVE column but if i made TIME (month or year) selection the value of PROGRESSIVE change on the base of time filters.

How can be possible obtain PROGRESSIVE values independently from filters ?

Thanks

Re: Progressive SUM, selection indipendent

swuehl — Mon, 09 Nov 2015 14:54:30 GMT

Have a look at

The As-Of Table

Re: Progressive SUM, selection indipendent

sunny_talwar — Mon, 09 Nov 2015 14:56:35 GMT

Try this expression with no accumulation rather than using full accumulation:

If(Sum(Money) > 0, RangeSum(Above(TOTAL Sum({<Month = , Year = >}Money), 0, RowNo(TOTAL))))

Re: Progressive SUM, selection indipendent

Anonymous — Mon, 09 Nov 2015 15:17:05 GMT

Hi,

you also can use this script:

LOAD [YEAR],

[MONTH],

[MONEY],

[MONEY] + if(Peek(YEAR, -1) <> YEAR, 0, Peek(acc_money, -1)) as acc_money

FROM ...;

Re: Progressive SUM, selection indipendent

Anonymous — Mon, 09 Nov 2015 15:33:12 GMT

Doesn't work...
Create exponential values that i don't understand:

MESE	YOUR EXPERSSION	CORRECT PROGRESSIVE	MONEY
01/10/2011	-	0	-
01/11/2011	-	0	-
01/12/2011	-	0	-
01/01/2012	€ 27.737.877,86	283039,57	283039,57
01/02/2012	€ 32.811.429,63	298460,7	15421,13
01/03/2012	€ 48.138.718,62	342128,19	43667,49
01/04/2012	€ 58.960.910,83	374241,52	32113,33
01/05/2012	€ 74.512.485,31	418548	44306,48
01/06/2012	€ 90.073.342,83	464586,04	46038,04
01/07/2012	€ 107.385.065,67	514332,37	49746,33
01/09/2012	€ 134.617.213,06	593599,27	32066,71

Re: Progressive SUM, selection indipendent

sunny_talwar — Mon, 09 Nov 2015 15:52:38 GMT

Would you be able to share a sample qvw?