Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kshitijas
New Contributor III

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

12 Replies

Re: Variance between two Months

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

kshitijas
New Contributor III

Re: Variance between two Months

Hello Sunny,

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

Qlik.png

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

kshitijas
New Contributor III

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

How about this:

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

kshitijas
New Contributor III

Re: Variance between two Months

Not working Sunny, Still getting null values

antoniotiman
Honored Contributor III

Re: Variance between two Months

Use Alt() function

Like

Alt(Sum(Amount),0)

kshitijas
New Contributor III

Re: Variance between two Months

qlikk.png

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

brunobertels
Valued Contributor

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

kshitijas
New Contributor III

Re: Variance between two Months

Hey Bruno,

Thanks for the reply but it doesnt work

Community Browser