topic Re: Pivot table help please. in QlikView

Pivot table help please.

Wed, 25 Nov 2020 16:16:04 GMT

I have attached QVW and the excel file.The monthyear from the linked table is throwing off the calculation.

Total Cum (Cumulative ) calculation is just getting getting calculated until the year end and also Forecast4 only belong to Aug but it is falling under few other moths as well.

Can anyone help me here.Any help is much appreciated.

Thanks.

Re: Pivot table help please.

Gysbert_Wassenaar — Wed, 10 Aug 2016 06:04:01 GMT

Your if condition is making sure that the year border isn't passed.

If(AddMonths(MonthStart(YearEnd(MonthYear)), IterNo() - 12) <= MonthYear, ...

Perhaps this blog post helps: The As-Of Table

Re: Pivot table help please.

sunny_talwar — Wed, 10 Aug 2016 08:00:41 GMT

I have responded to your request here:

Re: How to calculate Cumulative total

Basically, just as Gysbert mentioned, I thought you only wanted to accumulate over a single year. If that is not the case, you can try the new script for the LinkTable:

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

Re: Pivot table help please.

Wed, 10 Aug 2016 11:42:15 GMT

Thank you Sunny.But I still face issue here.If you see I only have FORECAST4 for the month of August but it is coming under all other months as well and also I only want the cumulative for Total Column...but it is doing cumulative for all other columns as well.Can you help me with the solution please.

Thank you stalwar1‌

Re: Pivot table help please.

sunny_talwar — Wed, 10 Aug 2016 12:16:59 GMT

I am not seeing any issue with FORECAST4 here

Re: Pivot table help please.

Wed, 10 Aug 2016 12:21:39 GMT

In the data file FORECAST 4 is only for August...but it is showing for all other months in the chart.

Re: Pivot table help please.

sunny_talwar — Wed, 10 Aug 2016 12:28:04 GMT

But the value itself is all 0. The column is shown because you still have Total Cum number available. Not really sure what the expectation is.

Re: Pivot table help please.

Wed, 10 Aug 2016 12:36:41 GMT

Sorry for the confusion.If you see the data file or Script only Aug has [Aug-16 Forecast4] where as all other months only has Forecast1,Forecast2,Forecast3 and then Total.But in the table FOrecsst4 is falling under all other months as well.

[Aug-16

Forecast1],

[Aug-16

Forecast2],

[Aug-16

Forecast3],

[Aug-16

Forecast4],

[Aug-16

Total],

[Sep-16

Forecast1],

[Sep-16

Forecast2],

[Sep-16

Forecast3],

[Sep-16

Total],

Re: Pivot table help please.

sunny_talwar — Wed, 10 Aug 2016 12:51:44 GMT

Try it now