topic Re: How to calculate Cumulative total in QlikView

How to calculate Cumulative total

Wed, 25 Nov 2020 16:16:04 GMT

I have attached the sample Qvw and the test file and I have to calculate the cumulative total.Can some one please help me here ?

I am having very hard time figuring it out.Thanks.

I have explained my requirement further in the qvw.

Re: How to calculate Cumulative total

Clever_Anjos — Mon, 08 Aug 2016 18:58:55 GMT

Use this technique: The As-Of Table

Re: How to calculate Cumulative total

sunny_talwar — Mon, 08 Aug 2016 23:05:18 GMT

Expanding your idea in the following example.

Script added:

LinkTable:

LOAD DISTINCT MonthYear as ReportYear,

Date(If(AddMonths(MonthStart(YearEnd(MonthYear)), IterNo() - 12) <= MonthYear, AddMonths(MonthStart(YearEnd(MonthYear)), IterNo() - 12)), 'MMM-YY') as MonthYear,

If(AddMonths(YearStart(MonthYear), IterNo() - 1) = MonthYear, 1) as Flag

Resident ProjectPipeline

While IterNo() <= 12;

Dim:

LOAD * Inline [

Dim

];

Re: How to calculate Cumulative total

Tue, 09 Aug 2016 01:52:26 GMT

syou are the best stalwar1‌Thanks much,...appreciate your kindness and time !!

Re: How to calculate Cumulative total

Wed, 10 Aug 2016 02:01:16 GMT

Hi Sunny, stalwar1‌

In this Cumulative total I am havinng an issue as my data file updated and additional months got added including 2017 months..so Total cumuulative is only adding up until Dec 16 .I want that to add on until all the existing months.How can I do that ?Any help is appreciated.

I have attached the sample file and the QVW.THANKS MUCH again.

Re: How to calculate Cumulative total

Wed, 10 Aug 2016 02:39:52 GMT

stalwar1‌ sorry to bother you.Any help is appreciated.Thanks much.

Re: How to calculate Cumulative total

sunny_talwar — Wed, 10 Aug 2016 07:59:13 GMT

Check now:

ProjectPipeline:

LOAD

Area,

PPRegion,

Market,

Date(Date#(Trim(SubField(Header, Chr(10), 1)), 'MMM-YY'), 'MMM-YY') as MonthYear,

Header as Header2,

SubField(Header, Chr(10), 2) as Header,

Value

Resident Table;

Left Join (ProjectPipeline)

LOAD Min(MonthYear) as Min_MonthYear

Resident ProjectPipeline;

DROP Table Table;

LinkTable:

LOAD DISTINCT MonthYear as ReportYear,

Date(AddMonths(MonthYear, IterNo() - ((Year(MonthYear) * 12 + Month(MonthYear)) - (Year(Min_MonthYear) * 12 + Month(Min_MonthYear)))), 'MMM-YY') as MonthYear,

If(AddMonths(MonthYear, IterNo() - ((Year(MonthYear) * 12 + Month(MonthYear)) - (Year(Min_MonthYear) * 12 + Month(Min_MonthYear)))) = MonthYear, 1) as Flag

Resident ProjectPipeline

While IterNo() <= ((Year(MonthYear) * 12 + Month(MonthYear)) - (Year(Min_MonthYear) * 12 + Month(Min_MonthYear)));