Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marleygt
Creator
Creator

Variance % and Δ Year vs Prev-Year Dynamically

Hi all!

I am in stuck with an expression who doesn't give right data.

Easily, what I am trying to reach is something I've already found on others discussions for Variance calculation but - I am doing something wrong for sure in my expression;

have a look here:

I need to calculate the "difference" in Variance % from one year to other (basically, ThisYear vs LastYear and LastYear vs PrevYear - 36 months range) considering "Absolute" and "Relative" and between Value and Volume

To be more clear:

                            2013                2014                    2015      

Company A     15.000.000          16.000.000         7.000.000

Company B       9.000.000         10.000.000          5.000.000

Company C       5.000.000           7.000.000          3.000.000

I Need to know Variance in % between Years and its relative % difference.

Now, the expression for calculating Variance I've used is the following:

=Sum({<Regions -={'5000'}>}Sales)/Sum({<Regions -={'5000'}>}Total Sales)

-

Sum({<Regions -={'5000'},Year=,Month=,CurYTD=,CurMat=,Livello2=,SUME=,Regions=,AN=,Date={'>=$(=Addyears(Min(Date),-1))<=$(=Addyears(Max(Date),-1))'}>}Sales)

/

(Sum({<Regions -={'5000'},Year=,Month=,CurYTD=,CurMat=,Livello2=,SUME=,Regions=,AN=,Date={'>=$(=Addyears(Min(Date),-1))<=$(=Addyears(Max(Date),-1))'}>}Total Sales))

Herewith you can find a mere sample of what I Mean (Expression Above is the one I use in real project - not in the sample).

The result are same: I can see a % that is not what I need, which is: if Variance in 2014 was 40% and in 2015 is 30%, I need to display the difference of 10%.

Is that possible?

Can you please help me find a way to reach the final goal?

Thank you all!

Alex

1 Solution

Accepted Solutions
krishna_2644
Specialist III
Specialist III

This should do.let me know.

View solution in original post

14 Replies
krishna_2644
Specialist III
Specialist III

Try using above function in your chart.

See attached.

sgrice
Partner - Creator II
Partner - Creator II

Just refer to the Column

above(Column(1))-Column(1)

or if it's a pivot table you may need to use before(Column(1)) etc

marleygt
Creator
Creator
Author

Thank you krishna chaitanya!

Seems working...and I've already implemented in real.

However...some data still doesn't give correct results, but maybe I need to fix something.

Apart from that, even in the sample you kindly resend me, I see that data value change on selection (ie: if you select Company data change and worse is Year selection who made "-" result).

Is possible to avoid this behavior?

Or - I don't know - maybe a way to fix just the result in a new column "disconnected" from other possible selections?

marleygt
Creator
Creator
Author

Thanks to you too sgrice!

Well...I am not really sure where to put your suggestion.

I try out on the sample, it still works, but it looks like no other selection is possible...like panels are freeze and selections have no effects.

      sUM(sales)/sUM(TOTAL sales)-above(Column(1))-Column(1)and (sUM(sales)/sUM(TOTAL sales))

Is that wrong?

krishna_2644
Specialist III
Specialist III

Please come again.

i did not understand the highlighted text.

Apart from that, even in the sample you kindly resend me, I see that data value change on selection (ie: if you select Company data change and worse is Year selection who made "-" result).

Is possible to avoid this behavior?

marleygt
Creator
Creator
Author

Here I am  krishna_2644

sorry for late answer, I was already gone.

Probably my bad english doesn't help...

however, here what I meant.

Look at the data in normal displaying:

normal.png

If I select any of the company:

Company Sel.jpg


If I select any of the year:

year sel.jpg

I don't understand why value data are changing and - or if its normal behavior to change, I need to know why they are

changing on selection.

(I am expecting difference data if I change year selection for instance, but not if I am selecting one of the company.

Why data are changing?)

Is more clear?

sgrice
Partner - Creator II
Partner - Creator II

If you are just wanting difference in Column 2 [%]

Just this may work better

above(Column(2))-Column(2)

Also if you do not need it to be a pivot Table then use straight table [They have less problems]

marleygt
Creator
Creator
Author

Ok.

I've tryed also with straight but still have some problem.

I will work on it a little and see if its possible to arrange something.

In the meanwhile, thank you!

krishna_2644
Specialist III
Specialist III

IS THIS WHAT YOU WANTED?