3 Replies Latest reply on Oct 21, 2016 12:07 PM by J. Nolfo

    Date Wildcards in IF statement

    J. Nolfo

      I looked for anything similar to this and could not find anything and looking for some help.

       

      I am creating some dynamic visualizations/dashboards that are based on two different parameters.  This first that allows you to pick the "As of Date" and the other is based on a calendar or fiscal year (we call it Season Year).  This Season Year starts on 10/1/xxxx.  Anything after that date has a Season Year based on the upcoming year.  So if the As Of Date is 10/21/16 and the season Year is selected, then the result needs to be 2017.  If the Calendar Year is selected, the result is 2016

       

      The visualizations I am designing are based on sales based on the "As Of Date" and the Year type.  I am trying to make it so I don't have to keep adding a new year into the formula.  The result of these formulas will feed into other calculations so that I don't have to create a new YoY calculation for each year.

       

      So this is my start, which works as it should:

       

      If [Parameters].[Year Type Selector] = "Calendar Year"

      THEN DATEPART('year',[As Of Date])

      ELSEIF [Parameters].[Year Type Selector] = "Season Year"

      AND [As Of Date]< #10/1/2016#

      THEN DATEPART('year',[As Of Date])

      ELSE DATEPART('year',[As Of Date])+1

      END

       

      What I am trying to accomplish is something like this, where the ** is a wildcard that could represent any year beyond 1999 (I only have data after 2011):

       

      If [Parameters].[Year Type Selector] = "Calendar Year"

      THEN DATEPART('year',[As Of Date])

      ELSEIF [Parameters].[Year Type Selector] = "Season Year"

      AND [As Of Date]< #10/1/20**#

      THEN DATEPART('year',[As Of Date])

      ELSE DATEPART('year',[As Of Date])+1

      END

      Any thought and help would be appreciated.

        • 1. Re: Date Wildcards in IF statement
          J. Nolfo

          I figured it out.  Not sure if this is the best way, but it works!

           

          If [Parameters].[Year Type Selector] = "Calendar Year"

          THEN DATEPART('year',[As Of Date])

          ELSEIF [Parameters].[Year Type Selector] = "Season Year"

          AND DATEPART('month',[As Of Date])< 10

          THEN DATEPART('year',[As Of Date])

          ELSE DATEPART('year',[As Of Date])+1

          END

          • 2. Re: Date Wildcards in IF statement
            David Li

            I'm a little confused. Are you basically trying to allow the user to choose what the value of ** is? If so, you could just use the DATE() function with an integer parameter, maybe like this:

            AND [As Of Date] < DATE("20" + STR([Parameter]) + "/10/01")

            • 3. Re: Date Wildcards in IF statement
              J. Nolfo

              David, good question.  The user would not be choosing the value of **, but would be choosing the "As of Date" parameter. 

               

              In essence, what I was trying to accomplish a dynamic calculated field for current year, prior year, and next prior year that was based on not only the "As of Date" parameter, but also the type of year parameter (Calendar or season).