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
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!
This should do.let me know.
Any Luck with th expression.
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
Awesome.Happy Qliking.