8 Replies Latest reply: May 28, 2014 9:30 AM by Michael Solomovich RSS

    Last year comparison

    Tjeerd Wieberdink

      Dear Reader,

       

      I want to compare YTD sales of this year with YTD sales of last year.

      Suppose I have a straight table with products A and B in the product dimension.

      The selected period is 2014.

      Expression 1 is sum(Sales)

      Expression 2 is sum({$<Date={$(='">='&yearstart(max(Date),-1)&'<='&addyears(max(Date),-1)&'"')},Year>} Sales)

       

      The table displays the data well. However, when i select 1 value in the product dimension of this chart, the set analysis will be limited to the associated dates for this product's sales selection. In the example the sales of last year will decline from 200 to 0 after selecting product A. I know it is associative but an enduser will never accept this!

      I used to think that these kinds of set analysis expressions are best practices for last year comparisons. However i have my doubts now. A possible solution would be a calendar data island and include the selection in that calendar into the set analysis of each expression i have.

      Another solution would be to ignore the selection by extending the set analysis with all possible dimensions i could possibly select in my app.

      Both solutions offer a lot of hassle to maintain. So i was wondering if anyone knows a good solution for this problem?

       

      Regards,

       

      Tjeerd

        • Re: Last year comparison
          whiteline _

          Hi.

           

          The best approach depends on the requirements

          You can add month # into calendar and use it for YTD period selection.

          The problem with your solution is that actually there is no time selection except year and you try to calculate it dynamically.

          • Re: Last year comparison
            ioannis giakoumakis

            I am attaching a sample app that can be helpfull, however I would suggest that you create flags in your reload script to handle this. See Calculating rolling n-period totals, averages or other aggregations

              • Re: Last year comparison
                Tjeerd Wieberdink

                Hi Ioannis and Whiteline,

                 

                I still experience the same problem. I can illustrate it better by using the qvw that Ioannis attached.

                Please go to the tab 'Set Analysis - Dynamic Advanced'.

                Select Salesperson 'Callins, Joan'

                Select Year '2007'

                Select Months Jan-Feb-Mar-Apr

                Please in the table in column (2006 (YTD)' the value 1.589 for product 'Chantell Shirt'

                Now select the product 'Chantell Shirt'.

                The value is decreased to 463.

                I think it is hard to explain the user why this value has decreased because the user didnt directly limit the period selection. He did it indirectly.

                How do you guys explain this? Or do you use a workaround?

                 

                Regards,

                 

                Tjeerd

                 

                 


              • Re: Last year comparison
                Michael Solomovich

                My preference, in situations like this, is to use function InYearToDate().  Here it would be:

                YTD       sum(if(InYearToDate(Date, today(), 0), Sales))

                LYTD     sum(if(InYearToDate(Date, today(), -1), Sales))

                 

                If you want to make it independent from some (or all) selections, just use set analysis.

                Similar can be used for MTD and QTD.

                Additional advantage of this method is that today() can be replaced with any date.  Typically I use a variable here.

                 

                Regards,

                Michael

                  • Re: Last year comparison
                    Tjeerd Wieberdink


                    Hi Michael,

                     

                    Thanks for the answer.

                    In my case year and month(s) selections are required. So the inyeartodate() function wont be useful because it cant analyse the excluded data.

                    'just use set analysis' is the core of the problem i guess. I thought i explained this in my last post. Maybe you missed something? Is my question unclear?

                     

                    Regards,

                     

                    Tjeerd

                      • Re: Last year comparison
                        Michael Solomovich

                        Yes, I've missed something - I've answered your opening question rather than the follow-up.

                        From the follow up, this specific problem can be resolved this way, see addition in bold:

                        sum({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))},ProductName=>} Month))"}>} LineSalesAmount)

                         

                        Hope it helps you to resolve other similar issues...

                          • Re: Last year comparison
                            Tjeerd Wieberdink


                            Hi Michael,

                             

                            Thanks for the quick reply.

                            For this particular example (chart and selection) it will work. However, in my opening post i suggested the same solution but concluded that it is a hassle to maintain.

                            This because in theory any dimension selected in the data cloud could cause the same problem.

                              • Re: Last year comparison
                                Michael Solomovich

                                Well, I still recommend using InYearToDate().  Requirement to select Year and Month can be replaced with setting the base date variable which can be in any year and month.  It is simple and comprehensive way.  Use {1} if you want to ignore all selections.

                                The method you prefer is OK. To minimize the hassle, use the same {1} to ignore all selections except explicitly specified.