12 Replies Latest reply: Jun 14, 2016 9:27 AM by Lydia Black

# Variance between two Months

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

• ###### Re: Variance between two Months

Do you have a sample you can provide with the expected output?

• ###### Re: Variance between two Months

Hello Sunny,

Cant uplaod the data but attaching an image for more clear understanding.

Now i have selected Jan and Feb so i got the variance as shown in image,in short based on selection of two months i sould get the variance.

My dimension is Month and

Expression is Sum(Cost Amount)

Thanks

• ###### Re: Variance between two Months

I used this formula:

Sum(Amount) - Above(Sum(Amount))

But it only work when data is present in both months, but when i select Jan and Feb

Jan has some value and feb is null den it gives me null only.

• ###### Re: Variance between two Months

RangeSum(Sum(Amount), -Above(Sum(Amount)))

• ###### Re: Variance between two Months

Not working Sunny, Still getting null values

• ###### Re: Variance between two Months

Use Alt() function

Like

Alt(Sum(Amount),0)

• ###### Re: Variance between two Months

In the expected output i am getting 265 where i should get -265

• ###### Re: Variance between two Months

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

• ###### Re: Variance between two Months

Hi

if you use a pivot table in qlik sense dont use above function but before() :

mesure variance

sum(amount) - before(sum(amount))

hope it helps

bruno

• ###### Re: Variance between two Months

Hey Bruno,

Thanks for the reply but it doesnt work

• ###### Re: Variance between two Months

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:

,MonthEndDate

,amount

FROm inputsource;

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

NoConcatenate

PriorMonthTable

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

,amount as PriorMonthAmount

RESIDENT InputTable;

FinalTable:

,amount - PriorMonthAmount as MonthlyVarianceAmount;

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

RESIDENT InputTable

LEFT JOIN (InputTable)

,YYYYMMint

,PriorMonthAmount

RESIDENT PriorMonthTable;

DROP TABLES,  InputTable, PriorMonthTable;

DROP FIELD YYYYMMInt;

Hope it helps