2 Replies Latest reply on Jul 6, 2016 6:52 PM by Ana Yin

    Custom date filters

    Ana Yin

      Hi all,

       

      Using the attached Superstore sample data, can anybody tell me if it is possible to create a single date filter with multi-dropdown values in this order:

       

      Year (just comparing CY and PY data, assume CY = 2014 and PY = 2013 in Superstore)

      HY1

      HY2

      QTR 1

      QTR 2

      QTR 3

      QTR 4

      Jan

      Feb

      Mar

      ...(so on until Dec)

       

      The purpose of this exercise is to compare CY and PY at each of the above 'date levels' (let's call it that). But rather than separating out the filters into Year, HY, QTR and Month, I'd like to bunch them all into a single filter. Is that possible?

       

      Thanks in advance

      A.

        • 1. Re: Custom date filters
          swaroop.gantela

          Ana,

           

          Please see if the attached could be a first step for you.


          It is a brute force method, but basically, one would need to

          create the output for each of the possibilities:

           

          CASE [SelectParameter]

              WHEN "Year" THEN DATETRUNC('year',[Order Date])

              WHEN "HY1" THEN IF [HY]="HY1" THEN MAKEDATE(YEAR([Order Date]),1,1) END

              WHEN "HY2" THEN IF [HY]="HY2" THEN MAKEDATE(YEAR([Order Date]),6,1) END

              WHEN "QTR1" THEN IF DATEPART('quarter',[Order Date])=1 THEN DATETRUNC('quarter',[Order Date]) END

              WHEN "Jan" THEN IF MONTH([Order Date])=1 THEN DATETRUNC('month',[Order Date]) END

          END

          2 of 2 people found this helpful
          • 2. Re: Custom date filters
            Ana Yin

            Thanks Swaroop! Appreciate it!