17 Replies Latest reply: Feb 5, 2017 9:00 AM by Maciej Rodak RSS

    Year over year comparison

    Maciej Rodak

      Hi,

      I would like create a chart that shows sales dynamic year over year within year dimension or month dimension.

       

      It's quite common as I expect, but I've searched through the resources i.e. Set Analysis for certain Point in Time

      and I've tried to implement it to my model but I didn't manage.

       

      This is the formula I've created to build year over year within year dimension

      Sum({<Category={"Sales"}>} Amount)/

      Sum({<Category={"Sales"},Year=${"Year-1"}>} Amount)

      I've tried many combinations, but I didn't find the proper solution.

       

      What is more if I want to have the formula working correctly within month dimension which means I would like to have my values ie. dived by 2016 - May over 2015 - May, this is something I'm unable to overcome currently.

        • Re: Year over year comparison
          Alluraiah Allu

          Hi

           

          if you have Month data like MMM-YYYY (May-2016)  as MonthYear , you can try this

           

           

          Current month (last year)   

           

           

                

                Sum({$<[MonthYear]={"$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>}Amount )

           

           

          Current month (this year)  

           

           

                          

          Sum({$<[MonthYear]={"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Amount )

           

           

          so for your situation :

           

          try this ..

           

           

           

          Sum({$<[MonthYear]={"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Amount ) /

          Sum({$<[MonthYear]={"$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"}>}Amount )

          • Re: Year over year comparison
            Maciej Rodak

            Thanks, I've added this date format to my MasterCalendar

            like that   Month(TempDate)&'-'&Year(TempDate)  AS MonthYear

             

            and then I've added this formula - exactly as Yours but with additional Category filter but it didn't work.

             

            Sum({$<[MonthYear]={"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},Category={"Sales"}>} Amount) /

            Sum({$<[MonthYear]={"$(=Date(AddYears(Max(MonthYear), -1), 'MMM-YYYY'))"},Category={"Sales"}>}Amount)

              • Re: Year over year comparison
                Alluraiah Allu

                Hi

                 

                1)Is your  Monthyear filed in MMM-YYYY format ?

                2)what output is coming when apply only one year  like following ..?

                 

                Sum({$<[MonthYear]={"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},Category={"Sales"}>} Amount)

                  • Re: Year over year comparison
                    Maciej Rodak

                    1) Yes

                    2) It gives 0 as a result.

                    • Re: Year over year comparison
                      Maciej Rodak

                      This is the effect of MMM-YYYY joining in MasterCalendar. When I sort it, it is sorted like it was a text, because "cze"  in Polish means "jun" in English. (below)

                       

                      Although I've used in another chart that kind of variable.

                       

                      Sum({$<Year={"$(=year(addyears(max(DocumentDate),-1)))"},Category={"Sales"}>}Amount)


                      It gives as a result Sales for the previous year correctly, but when I try to use it


                      something like that, it gives me for some variants good results, but when I analyse data with yearly dimension it is not good.

                      Sum({$<Category={"Sales"}>}Amount)/

                      Sum({$<Year={"$(=year(addyears(max(DocumentDate),-1)))"},Category={"Sales"}>}Amount)

                      Data_170104.png

                    • Re: Year over year comparison
                      Vineeth Pujari

                      Create a MonthYear field like below

                      DATE(MonthStart(DocumentDate,'MMM-YYYY')) as MonthYear

                       

                      Expression for Previous Year Same Month

                      Sum({$<[MonthYear]={"$(= Date(AddYears( Date(max(DocumentDate)),-1),'YYYYMM') )"},Category={"Sales"}>}Amount)

                        • Re: Year over year comparison
                          Maciej Rodak

                          If I want to generate the date like You suggested

                           

                          DATE(MonthStart(DocumentDate,'MMM-YYYY')) as MonthYear

                           

                          I have to change "DocumentDate" into "TempDate" from my Master Calendar. But it doesn't show any results - this dimension is without any data.

                           

                           

                          However in Your second formula MonthYear is compared with the format YYYYMM which is not the format for MonthYear = MMM-YYYY.

                           

                          If I compare it with the dimension I've created with the format YYYYMM it still doesn't work properly.

                           

                          It works fine for months when I use that kind of formula like below. However it's not good when I want to analyze data over Year dimension which is the problem for now.

                           

                          Sum({$<Category={"Sales"}>}Amount)/

                          Sum({$<Year={"$(=year(addyears(max(DocumentDate),-1)))"},Month-={">$(=$(varMonthMax))"},Category={"Sales"}

                          >}Amount)

                          where

                          varMonthMax=Num(Month(max(DocumentDate)))

                      • Re: Year over year comparison
                        Maciej Rodak

                        Still no idea, I can handle with the issue comparing year over year when in dimension on X - axis is month. However it doesn't work when I need to have Year there. Suggestions above didn't work for me or I implemented them wrong.

                         

                        I would like to have values in my chart that could show my Year over Year Sales dynamics.

                        • Re: Year over year comparison
                          vj kri

                          Can you please share ur app, or app with sample data and where the logic is not working?

                            • Re: Year over year comparison
                              Maciej Rodak

                              Hi,

                              Thank you for your reply. This is my sample app. There is no problem for YoY comparison over months, by over Year dimension it doesn;t work.

                               

                              Maciek

                                • Re: Year over year comparison
                                  vj kri

                                  Hi, For year on year, You want to compare the current yr/last yr numbers rite?. i have added the logic for u in the app. Please check and let me know.

                                    • Re: Year over year comparison
                                      Maciej Rodak

                                      Hi,

                                       

                                      Yes, I would like to compare the year with the previous year. But I would like to have it compared in one bar chart and in percent.

                                       

                                      Your app works fine if I analyze year over year but with months

                                       

                                      If I divide it, it is the format required (one series):

                                      Sum({$<Category={"Sales"},Year={"$(=max(Year))"}>}Amount)

                                      /

                                      Sum({$<Category={"Sales"},Year={"$(=max(Year)-1)"}>}Amount).

                                       

                                      However I would like to get more complex result as well: for year:

                                       

                                      So on my X axis If I have:

                                       

                                      Year 2016 I would like to have value on Y axis "Sales 2016/Sales 2015"

                                      Year 2015 I would like to have value on Y axis "Sales 2015/Sales 2014"

                                      Year 2014 I would like to have value on Y axis "Sales 2014/Sales 2013" (in the database there is no data for 2013 so it would result in 0)

                                      Year 2013 I would like to have value on Y axis "Sales 2013/Sales 2012" (in the database there is no data for 2012 so it would result in 0)

                                      Just like on the screen below.

                                       

                                      Is it feasible?

                                      chart_1.png

                                • Re: Year over year comparison
                                  vj kri

                                  Hi, I'm afraid it is not possible as set analysis is done considering the dimensions. There is not direct way to do the above requirement.

                                  • Re: Year over year comparison
                                    Maciej Rodak

                                    Because I couldn't get the effect with set analysis I've created an additional View using SQL.

                                    I have moved bacward the period in the table where is the structure of the company and that is hov I get the desired effect. It's not ideal but it works fine for me.

                                     

                                    Maciek

                                     

                                    YearMonth_170113.png

                                    • Re: Year over year comparison
                                      Kordian Justek

                                      Hi Maciej

                                       

                                      Is this the only way to achieve result? I think some combination of variables and TOTAL tag in expression is the right way.

                                       

                                      Przemysław Staniszewski

                                        • Re: Year over year comparison
                                          Maciej Rodak

                                          Hi Przemek,

                                           

                                          I believe it's not the only way to do this. However, I have put a lot of effort to achieve it and I've failed to get satisfactory results. In my opinion using TOTAL is not the direction I would search for the solution of the problem. As I understand TOTAL, it would bring the total sum of the measure, while I would like to compare values from the specific period to the values from the relevant period but a year before (i.e. a year or a month).

                                           

                                          I think maybe the clue is in attached document somewhere in the point "4.6.1 Numeric function".

                                           

                                          Maciek