3 Replies Latest reply: May 18, 2017 12:15 PM by Justin Taylor RSS

    create date range variable for rolling 13 months

    Justin Taylor

      I am working on a chart that needs to show a default view of the last 13 months.

      I want to reuse the same 13 month range for other visualizations so I’ll use a variable.

      I think my set analysis should look like this:

      Count({<$(vAriable)>}Distinct Field1)

       

      I have the begin and end dates calculating correctly – when viewed through a KPI. 

      date(MonthName(addmonths(vToday),-13)) gives me April 1, 2016 (which is correct)

      date(MonthEnd(vToday))    gives me May 31, 2017 (which is correct)

      vToday = Today()

         

      I now need to put the range together in a format that will work in the variable – but I need some help please.  thanks

      This is where I am now…

       

      {">$(=date(MonthName(addmonths(vToday),-13))) <=$(=date(MonthEnd(vToday)))"}

       

        • Re: create date range variable for rolling 13 months
          Aar Kay

          May be this as variable

          vTest='>'& (date(MonthStart(addmonths(Today(),-13)))) &'<='&(date(MonthEnd(Today())))

           

          sum({<[Order Date]={"$(=$(vTest))"}>}Sales)

          • Re: create date range variable for rolling 13 months
            Chris Cammers

            What you have there is a little bit of tricky syntax. I have found that you have to calculate the entire string, you cannot just rely on the dollar sign expansion to concatenate the string together ">$()<=$()" syntax does not work. so something like this should do the trick... $(='>' & date(MonthName(addmonths(vToday),-13)) & '<=' & date(MonthEnd(vToday)))

            I find that with this kind of expression you also need to make sure other calendar selections are not conflicting with the 13 month set analysis.

              • Re: create date range variable for rolling 13 months
                Justin Taylor

                Thanks Chris - your last sentence gets to the root of the problem.  We need to be able to select dates within and also outside that 13 month range and have the graph update.  This is what I ended up putting together:

                 

                My variable $(vRolling13) = MonthSequence = {">$(=max(MonthSequence)-14)<=$(=max(MonthSequence))"}

                 

                Monthsequence is just a number count assigned to each month in the master calendar. 

                 

                Count({<$(vRolling13) >} Distinct Value1)

                 

                thanks!