14 Replies Latest reply: Jul 24, 2015 10:13 AM by Krishna Chaitanya RSS

    Variance % and Δ Year vs Prev-Year Dynamically

    Alessandro Marletti

      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