Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This should do.let me know.
Try using above function in your chart.
See attached.
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
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?
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?
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?
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?
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]
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!
IS THIS WHAT YOU WANTED?