6 Replies Latest reply: Feb 18, 2013 4:15 AM by Alexis Hadjisoteriou RSS

    Set Analysis and Dates

    Alexis Hadjisoteriou

      Hi,

       

      I need to build functionality that enables the user to select one or more consecutive months (say June and July 2012) and we need to present 2 sets of results, one for the selected months (June and July 2012) and another that we can "Previous Year" that shows the results for the same period in the previous year (June and July 2011 in this example)

       

      For my dataset I created a Master Calendar that includes a field called MonthID which is the month number staring from "1" for the oldest month and incremented by 1 for every month after that, e.g.

       

       

      Date
      MonthID
      1/1/20061
      10/2/20062
      11/3/20063

      etc
      1/6/201154
      1/6/201266
      1/7/201267
      1/12/201272

       

      I am using Set Analysis to calculate the the "PreviousYear"  which works for almost all scenaria except in the case where a December (Any December) is the month selected (or the minimum of the months selected (e.g. December 2012 and January 2013)

       

      [code]

       

      {$<MonthID = {">=$(=min(MonthID) - 12)"},

      Date = {"<$(=Date(MakeDate(Year(Max(Date))-1, Month(Max(Date))+1, 1),'DD/MM/YYYY'))"},

      Year = ,

      Quarter = ,

      Period = ,

      [Period (#)] = ,

      Month = >}

       

      [/code]

       

      To see how the above works, assume that June and July 2012 was chosen:

      The above set analysis will generate:

       

      MonthID >= min(MonthID) -12

      which is

      MonthID >=66-12

      which is

      MonthID>=54

       

      Also, the 2nd line of my set analysis generates:

      Date < MakeDate(2011, 8, 1)

      which is

      Date < 1/8/2011

       

      so to get the data for "PreviousYear" for the selected range of June and July 2012:

      MonthID >= 54

      Date < 1/8/2011

       

      PROBLEM:

      If the user selects December 2012

       

      MonthID >= min(MonthID) -12

      which is

      MonthID >=72-12

      which is

      MonthID>=60

       

      and

      Date < MakeDate(2011, 13, 1)

      which is

      an invalid date as there is no month 13!!!!

       

      Can anyone help me get 1/1/2012 and the maximum date for the above so that the data set will be based on:

      MonthID >= 60

      Date < 1/1/2012

        • Re: Set Analysis and Dates
          Stefan Wühl

          Instead of using a MonthID, I think you should be able to use only an advanced search in Date field, using an lower as well as an upper limit.

           

          But coming back to your initial request, try something like

           

          Date = {"<$(=Date(addmonths(monthstart(Max(Date)),-11),'DD/MM/YYYY'))"}

            • Re: Set Analysis and Dates
              Alexis Hadjisoteriou

              Hi swuehl,

               

              Your suggestion was very helpful indeed as it calculates correctly the "Date" part as 1/1/2012 in the case described above.

               

              That alone however does not generate the right result because it does not have a lower limit which was the reason I introduced the MonthID concept.

               

              Just so that you have the complete story, the formula in question is stored in a variable (say "vSetPreviousYear)) and in my chart I simply say:

               

              = Sum($(vSetPreviousYear) Sales)

               

              Can you assist to complete the formula to cater for the correct range .....

                • Re: Set Analysis and Dates
                  Sampath Kumar

                  Alexis,

                   

                  Hi,

                  Please check

                  Month(Max(Date))+1 will not be 13. it will be next month to the Max(Date).

                  IF Max of date is 'dec' then Month(Max(Date))+1 will be Jan.

                   

                  (I.e) MakeDate(2012,month(12)+1,1)

                    • Re: Set Analysis and Dates
                      Alexis Hadjisoteriou

                      Hi Sampath,

                       

                      Thank you for responding.

                       

                      I think it's my fault for not explaining the exact requirement.

                       

                      If the user selects the range:

                      June2012 - August 2012 then the result should be all data for the range

                      June 2011 - August 2011

                       

                      Likewise, if they select:

                      Dec 2012

                      then the resultset should comprisedata for

                      Dec 2011.

                       

                      The previous respondent dealt with the issue of a December selection where my formula:

                      Date = {"<$(=Date(MakeDate(Year(Max(Date))-1, Month(Max(Date))+1,


                      was flawed because in the case of December it was attempting to calculate Date <= 1/13/yyyyy

                      As you rightly state there is no such thing as month 13!!

                       

                      swuehl's response takes care of the upper limit for the data set - using his suggestion on its own:

                       

                      Date = {"<$(=Date(addmonths(monthstart(Max(Date)),-11),'DD/MM/YYYY'))"}

                       

                      will return (in the case of a December 2012 selection for example) data that meets the criterion:

                      data < 1/1/2012

                      This is incomplete as we are interested in:

                      1/12/2011 <= data < 1/1/2012

                       

                      What we are missing is the lower limit of the dataset and that is why, in my original formula I introduced the concept of MonthID (see my original post).

                       

                      To be honest, the simplest way to resolve this is to finetune my original definition (see below) and just base the selection by extending the MonthID line to read:

                       

                      Month ID >=min(MonthID)-12

                      AND

                      MonthID <max(MonthID)-11

                       

                      (I am missing the part in bold above)..

                       

                      Can someone help?

                       

                      Alexis

                       

                       

                       

                       

                       

                       

                       

                      [code]

                       

                      {$<MonthID = {">=$(=min(MonthID) - 12)"},

                      Date = {"<$(=Date(MakeDate(Year(Max(Date))-1, Month(Max(Date))+1, 1),'DD/MM/YYYY'))"},

                      Year = ,

                      Quarter = ,

                      Period = ,

                      [Period (#)] = ,

                      Month = >}

                       

                      [/code]