Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

svedesee
New Contributor II

Progressive SUM, selection indipendent

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

5 Replies
MVP
MVP

Re: Progressive SUM, selection indipendent

Have a look at

The As-Of Table

Re: Progressive SUM, selection indipendent

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

josemanuel_cayo
New Contributor III

Re: Progressive SUM, selection indipendent

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

svedesee
New Contributor II

Re: Progressive SUM, selection indipendent

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

 

MESEYOUR EXPERSSION          CORRECT             PROGRESSIVE               MONEY
01/10/2011-0-
01/11/2011-0-
01/12/2011-0-
01/01/2012€ 27.737.877,86283039,57283039,57
01/02/2012€ 32.811.429,63298460,715421,13
01/03/2012€ 48.138.718,62342128,1943667,49
01/04/2012€ 58.960.910,83374241,5232113,33
01/05/2012€ 74.512.485,3141854844306,48
01/06/2012€ 90.073.342,83464586,0446038,04
01/07/2012€ 107.385.065,67514332,3749746,33
01/09/2012€ 134.617.213,06593599,2732066,71

Re: Progressive SUM, selection indipendent

Would you be able to share a sample qvw?

Community Browser