Variance between two Months

[Karahs]

Hello everyone,

               I have a pivot table in which there are month columns ie. April-March.I want to calculate variance between the two selected months.

Eg. If i select April and March i should be getting the vairance between them in the next column called variance in a pivot table.

Thanks & Regards,

Kshitija

[sunny_talwar]

Would you be able to share few rows of data to replicate what you have in the chart?

[Karahs]

Data attached in the below link:

Variance between months‌

[lydiablack]

I find doing the work in the load script makes for the most flexible and accurate variance info at grain level

Assumes monthly level data input (syntax will be far from perfect), same concept for any time frames.

Join original table to itself where month is 1 prior

InputTable:

Load Account

        ,MonthEndDate

        ,amount

FROm inputsource;

//make an alias version to join 'real' month to prior month

NoConcatenate

PriorMonthTable

Load Account

        ,Year(MonthEndDate)*12 + Month(MonthEndDate) -1 as YYYYMMInt

       ,amount as PriorMonthAmount

RESIDENT InputTable;

FinalTable:

Load *

        ,amount - PriorMonthAmount as MonthlyVarianceAmount;

Load *

        ,Year(MonthEndDate)*12 + Month(MonthEndDate) as YYYYMMInt

RESIDENT InputTable

LEFT JOIN (InputTable)

Load Account

        ,YYYYMMint

        ,PriorMonthAmount

RESIDENT PriorMonthTable;

DROP TABLES,  InputTable, PriorMonthTable;

DROP FIELD YYYYMMInt;

Hope it helps