topic Re: Cumulation in QlikView

Cumulation

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I need a solution for the below scenario.

If i have a data from 2000-Jan to till date and I want to show the data for 2016-jan to 2016-Nov(Current month). Sum value that I want to see in 2016-Jan is cumulative value from 2000-Jan to 2016-Jan. For 2016-Feb, cumulative value from 2000-Jan to 2016-Feb and so and so forth till 2016-Nov(Current month).

Expected Result:

Date	Total Sales
Jan-16	12740
Feb-16	12792
Mar-16	12851
Apr-16	12919
May-16	12977
Jun-16	13019
Jul-16	13115
Aug-16	13210
Sep-16	13289
Oct-16	13333
Nov-16	13394

Regards

Srinivas

Re: Cumulation

rubenmarin — Thu, 24 Nov 2016 15:25:17 GMT

Hi SrinivasaRao, for that sample you can use this script:

LOAD Date,

Year(Date) as Year,

Sales,

RangeSum(Sales, Peek(SalesAcum)) as SalesAcum

FROM [.\SampleData.xlsx] (ooxml, embedded labels, table is Sheet1);

For the table you only need Date as dimension and this expression:

Only({<Year={$(=Max(Year))}>} SalesAcum)

or if you want to ignore selections:

Only({1<Year={$(=Max({1} Year))}>} SalesAcum)

Re: Cumulation

ElizaF — Thu, 24 Nov 2016 16:01:51 GMT

Hi,

You can use also the The As-Of Table concept.

The As-Of Table