Solved

[microwin88x]

Hello,

I have the following table with fields PERIOD and AMOUNT:

PERIOD	AMOUNT	ACUM	OP
201301	448	448	448
201302	390	838	419
201303	499	1337	445,666667
201304	153	1490	372,5
201305	460	1950	390
201306	336	2286	381
201307	333	2619	374,142857
201308	429	3048	381
201309	346	3394	377,111111
201310	22	3416	341,6
201311	11	3427	311,545455
201312	356	3783	315,25
201401	148	148	148
201402	184	332	166
201403	356	688	229,333333
201404	245	933	233,25
201405	34	967	193,4
201406	324	1291	215,166667
201407	300	1591	227,285714
201408	332	1923	240,375
201409	259	2182	242,444444
201410	459	2641	264,1
201411	481	3122	283,818182
201412	31	3153	262,75
201501	306	306	306
201502	98	404	202
201503	373	777	259
201504	176	953	238,25
201505	320	1273	254,6

I need to calculate the fields ACUM and OP by script where:

ACUM for 201301 is 448, ACUM for 201302 is 448+390=838, ACUM for 201303 is 448+390+499=1337

OP for 201301 is 448/01, OP for 201302 is 838/02, OP for 201303 is 1337/03

Do you know how could I do this?

Thank you!

Solved

[Chanty4u]

hi,

RangeSum(Above(TOTAL Sum(PERIOD), 0, RowNo(TOTAL)))/sum(total AMOUNT)

or

RangeSum(Above(TOTAL Sum(PERIOD), 0, RowNo(TOTAL)))/sum(total PERIOD)

hope this helps you

thanks

suresh

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD *,

  YEAR(PERIOD) as YEAR,

  Num(Month(PERIOD)) as MONTH;

LOAD Date#(PERIOD, 'YYYYMM') as PERIOD,

    AMOUNT

FROM

[https://community.qlik.com/thread/190352]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  ACUM/MONTH as OP;

LOAD *,

  If(YEAR = Peek('YEAR'), RangeSum(PEEK('ACUM'), AMOUNT), AMOUNT) as ACUM

Resident Table;

DROP Table Table;