5 Replies Latest reply: Jul 16, 2018 8:56 AM by Petter Skjolden RSS

    Default Date selection in Set Analysis

    Villyee Anderson

      In my data model, i have dateds from 01-Jan-2017 to 31-Dec-2020.

       

      Current date is 15 Jul 2018, then i want to display default data using set analysis as current date -6 months and +17 months.

      It means from 01-Jan-2018 to 31-Dec-2019.

       

      I can not able to create variable using today() function cause if user select Aug-18 then it should as from 01-Feb-2018 to 31-Jan-2020 and I don't think so i can use max date also cause max date is 31-Dec-2020.

       

      Please suggest if there is any possible option is available.

       

      Thanks,

      Villyee

        • Re: Default Date selection in Set Analysis
          Milind Deshmukh

          Try using below expression

           

          sum({<CalendarDate={">=($(=AddMonths(Date(Max(CalendarDate)),-6)))

          =<($(=AddMonths(Date(Max(OrderDate)),+12)))"}>}SalesAmount)

            • Re: Default Date selection in Set Analysis
              Villyee Anderson

              Thanks Milind for your valuable reply.

               

              But my requirements is not like this, in my calendar I have max date 31 Dec 2020 and as per your logic if I take

              max(CalendarDate) and -6 then it will give me Jul-20 but I want current date -6 that is Jan-18 and that should be dynamic like if user click on Aug-18 then it should -6 from Aug-18 i.e. Feb-18.

               

              Thanks.

            • Re: Default Date selection in Set Analysis
              Petter Skjolden

              You have three possible selection scenarios:

               

              1) The user hasn't selected anything and then the current date - today - should be used as reference date.

              2) The user has selected a single date and then that date should be the reference date.

              3) The user has selected multiple dates and then either the first or the last date should be the reference date.

               

               

              A) This can be done by creating four variables and using the last variable vRefDateRange in the set expression:

               

              vRefDate:

              =Date(If( GetSelectedCount(aDate)=0 , Today(1) , Min(aDate) ) )

               

              vRefDateRangeMin:

              =MonthStart(AddMonths(vRefDate,-6))

               

              vRefDateRangeMax:

              =MonthEnd(AddMonths(vRefDate,12))

               

              vRefDateRange:

              ='>=$(vRefDateRangeMin)<=$(vRefDateRangeMax)'

               

              Sum( {<aDate={"$(vRefDateRange)"}>} Sales )

               

               

              B) Another way of making it more maintainable and testable using only a single variable could be:

               

              vRefDate:

              =AddMonths(If(GetSelectedCount(aDate)=0, Today(1) , Date(Min(aDate)) ) , $1 )

               

              Sum( {<aDate={">=MonthStart($(vRefDate(-6)))<=MonthEnd($(vRefDate(12)))"}>} Sales )

               

               

              C) You could of course hard-code the entire expression in a single set expression without using variables - although I think this is much harder to debug, maintain and reuse:

               

              Sum( {<aDate={">=$(=MonthStart(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),-6)))<=$(=MonthEnd(AddMonths(If(GetSelectedCount()=0,Today(1),Date(Min(aDate))),12)))"}>} Sales )