6 Replies Latest reply: Jul 11, 2012 11:44 AM by Michael Solomovich RSS

    two different kind of periods

      Hello,

       

      Can anyone tell me what the syntax is to compare 2 different kind of periods? Thereby i want to have the choice to choose a specific date (e.g. 10-07-2012) or a month or a year or a period of days (e.g. 01-07-2012 to 08-07-2012).

       

      This then should be compared with last year, so:

       

      10-07-2012 vs 10-07-2011

       

      or

       

      month vs month last year

       

      or

       

      (this) year vs last year

       

      or

       

      01-07-2012 to 08-07-2012 vs 01-07-2011 to 08-07-2011

       

       

      Something tells me this must be a very simple formula but i cannot put my finger on the right one.

       

      Thanks!

        • Re: two different kind of periods
          Michael Solomovich

          It is simple.  Use functions InMonthToDate() and InYearToDate() in conditions.

          (Sorry, have to run now, will be back tomorrow if help is still needed by that time)

          • Re: two different kind of periods
            Michael Solomovich

            OK here it is.


            Assuming you need sum(Sales), and there is SalesDate field.  Your specific date (base date  10-07-2012) - let use a variable for it, call it BaseDate.  So, the calcualtions will be:
            MTD:  sum(if(InMonthToDate, SalesDate, BaseDate, 0), Sales)
            MTD last year: sum(if(InMonthToDate, SalesDate, BaseDate, -12), Sales)
            Last MTD:  sum(if(InMonthToDate, SalesDate, BaseDate, -1), Sales)
            YTD:  sum(if(InYearToDate, SalesDate, BaseDate, 0), Sales)
            Last YTD:  sum(if(InYearToDate, SalesDate, BaseDate, -1), Sales)

             

            Now you can easy compare the parts you want.  for example, difference between sales MTD and last year MTD:
            sum(if(InMonthToDate, SalesDate, BaseDate, 0), Sales) - sum(if(InMonthToDate, SalesDate, BaseDate, -12), Sales)

            And you can change Base Date if needed.

             

            Regards,
            Michael

              • Re: two different kind of periods

                Hello Michael,

                 

                 

                 

                Thank you for your help.

                 

                 

                 

                I’ve looked at your formulas but these are all fixed periods. My wish is to select any random period. Is that possible?

                 

                 

                 

                Met vriendelijke groet,

                 

                Rene Santifort

                Drogisterij.net, veilig en vertrouwd

                Voor 21:00 uur besteld, morgen in huis

                  • Re: two different kind of periods
                    Michael Solomovich

                    Rene, could you please provide an example of "random period"?

                      • Re: two different kind of periods

                        Say, i want to compare 1-7-2012 with 1-7-2011

                         

                         

                         

                        and on another day a want to compare 4-3-2011 to 4-6-2011 with 4-3-2010 to 4-6-2010

                         

                         

                         

                        So every date range i choose (this could be a day, a date range, a week, a month, a year) should be choosable and be compared with the year before.

                         

                         

                         

                        Hopefully this clarifies things.

                         

                         

                         

                        Regards,

                         

                        Rene Santifort

                        Drogisterij.net, veilig en vertrouwd

                        Voor 21:00 uur besteld, morgen in huis

                          • Re: two different kind of periods
                            Michael Solomovich

                            It is a little different, and a little more complex, but not impossible.  Following is an example with set analysis where you get the previous year data by selecting current year dates (separate days, or months, etc.)

                             

                            For the current year it is simple:
                            sum(Sales)

                             

                            To get previous year, same days, create a variable, let's call it DateList, defined like this:
                            =chr(39) & concat(addyears(SalesDate,-1), chr(39) & ',' & chr(39)) & chr(39)

                             

                            It contains comma-separated list of dates one year back.  If you select SalesDate = 07/15/2012, the variable creates string '07/15/2011'.  If you select multiple dates (directly or by selecting months), variable contains all these dates.

                            And, the expression to calculate last year data is:
                            sum({<Month=, Year=, SalesDate={$(DateList)}>} Sales)

                             

                            If you need something like this for the previous month instead of the previous year, the variable is a little different:
                            =chr(39) & concat(addmonths(SalesDate,-1), chr(39) & ',' & chr(39)) & chr(39)

                             

                            Hope this is what you need.

                             

                            Regards,
                            Michael