Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

marleygt
Contributor

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
Valued Contributor III

Re: Variance % and Δ Year vs Prev-Year Dynamically

This should do.let me know.

14 Replies
krishna_2644
Valued Contributor III

Re: Variance % and Δ Year vs Prev-Year Dynamically

Try using above function in your chart.

See attached.

sgrice
Contributor II

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Contributor

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Contributor

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Valued Contributor III

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Contributor

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Contributor II

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Contributor

Re: Variance % and Δ Year vs Prev-Year Dynamically

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
Valued Contributor III

Re: Variance % and Δ Year vs Prev-Year Dynamically

IS THIS WHAT YOU WANTED?