Pivot table help please.

[#1]

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.

[Gysbert_Wassenaar]

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

[sunny_talwar]

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

[#4]

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‌

[sunny_talwar]

I am not seeing any issue with FORECAST4 here

[#6]

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

[sunny_talwar]

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.

[#8]

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],

[sunny_talwar]

Try it now