topic How to script that cumsum? in QlikView

How to script that cumsum?

bernarducs — Sat, 16 Nov 2024 19:13:18 GMT

Pessoal é o seguinte:

I got a data series month to month. There, I have the total of jobs in every december and its balance (could be a positive or negative value) in the other months.

Goal: do a 12 month cumulative sum starting from December of year X to next November X+1. CumSum from December X+1 to November X+2 and so on...

Like the pic above.

How can I solve by scripting?

Re: How to script that cumsum?

Gysbert_Wassenaar — Tue, 28 Jan 2020 13:59:48 GMT

something like this:

tmp:
LOAD ano_mes, sum(valores) as sumValores FROM ...source... GROUP BY ano_mes;

Result:
LOAD
    ano_mes,
    If(Right(ano_mes,2)=12, sumValores, rangesum(sumValores, peek(cumsumValores)) as cumsumValores
RESIDENT
    tmp
ORDER BY
   ano_mes
   ;

DROP TABLE tmp;

Re: How to script that cumsum?

Brett_Bleess — Mon, 24 Feb 2020 15:21:34 GMT

Here is a Design Blog post to check out too:

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Here is the base URL for this area from which you can search further:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Here is another site from one of our MVPs that may have some stuff too:

https://qlikviewcookbook.com/

If the other post did work for you, be sure to return to your thread and use the Accept as Solution button on that post to give them credit for the help and let other Members know that worked for you.

Regards,
Brett