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
          Allu 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
                Allu 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
                          vijayakrishna parimi

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

                          • Re: Year over year comparison
                            vijayakrishna parimi

                            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