topic Re: Variance % and Δ Year vs Prev-Year Dynamically in QlikView

Variance % and Δ Year vs Prev-Year Dynamically

marleygt — Wed, 22 Jul 2015 14:13:03 GMT

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

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

Qrishna — Wed, 22 Jul 2015 14:55:20 GMT

Try using above function in your chart.

See attached.

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

sgrice — Wed, 22 Jul 2015 15:13:32 GMT

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

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

marleygt — Wed, 22 Jul 2015 15:27:47 GMT

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?

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

marleygt — Wed, 22 Jul 2015 15:32:40 GMT

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?

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

Qrishna — Wed, 22 Jul 2015 15:55:49 GMT

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?

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

marleygt — Thu, 23 Jul 2015 07:18:38 GMT

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:

If I select any of the company:

If I select any of the year:

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?

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

sgrice — Thu, 23 Jul 2015 07:28:38 GMT

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]

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

marleygt — Thu, 23 Jul 2015 08:03:09 GMT

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!

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

Qrishna — Thu, 23 Jul 2015 14:16:24 GMT

IS THIS WHAT YOU WANTED?

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

marleygt — Thu, 23 Jul 2015 15:21:53 GMT

Thank you very much krishna_2644!

Well...I think you are almost very close to right solution!

The result data who didn't change on selection are ok but, I need to let the graph be dynamic...

I mean: basic purpouse is when customer select "Company A", only "Company A",, if Company B, just B should be displayed...and so far for Year...

If should be possible to make that, I think right solution will be found!

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

Qrishna — Thu, 23 Jul 2015 18:04:29 GMT

This should do.let me know.

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

Qrishna — Thu, 23 Jul 2015 20:21:01 GMT

Any Luck with th expression.

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

marleygt — Fri, 24 Jul 2015 07:54:28 GMT

YAHOO!

Simply A - M - A - Z - I - N - G!

Thank you krishna !

It's working perfectly and it was exactly what I was looking for! Thank you very much!

(found in the script the peek function! Hope to be able to implement in my original project...argh! )

Have a nice day!

Alex

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

Qrishna — Fri, 24 Jul 2015 14:13:59 GMT

Awesome.Happy Qliking.