3 Replies Latest reply on Mar 7, 2017 8:10 AM by Joe Oppelt

    Date Parameter using different data types

    Alec Chen

      Hi,

      My datasource has MONTH formatted as YYYYMM, and I would like to create a parameter (which will control month as a filter) that reads the actual month, i.e. January, February etc.

       

      Thinking through this, I figure I need to create a calculated field first to transform YYYYMM into text (these are based on a 4-5-4 calendar):

       

      IF [Month 454 Skey]="201701" THEN "February"

      ElseIf [Month 454 Skey]="201702" THEN "March"

      ElseIf [Month 454 Skey]="201703" THEN "April"

      ElseIf [Month 454 Skey]="201704" THEN "May"

      ElseIf [Month 454 Skey]="201705" THEN "June"

      ElseIf [Month 454 Skey]="201706" THEN "July"

      ElseIf [Month 454 Skey]="201707" THEN "August"

      ElseIf [Month 454 Skey]="201708" THEN "September"

      ElseIf [Month 454 Skey]="201709" THEN "October"

      ElseIf [Month 454 Skey]="2017010" THEN "November"

      ElseIf [Month 454 Skey]="2017011" THEN "December"

      ElseIf [Month 454 Skey]="2017012" THEN "January"

      ELSE [Month 454 Skey] END

      However, I'm not sure where to go from here. Any pointers would be greatly appreciated.

       

       

      Thanks.

        • 1. Re: Date Parameter using different data types
          Joe Oppelt

          You can have a numeric value for the parameter list (or even a character value)

           

          1

          2

          3

          4

          5

          6

          7

          8

          ...

           

          And you can have a totally different value for the "Display Value"

           

          1     February

          2     March

          3     April

          4     ...

          5

          6

          7

          8

          ..

           

          The actual parameter value and the display value don't have to be of the same data type.

           

          So the user sees:

           

          February

          March

          April

          etc

           

          But internally if you use the parameter in a calc, the actual value is 1, 2, 3 ...  (or "01", "02", "03", ...  depending on how you set it up.)

          1 of 1 people found this helpful
          • 2. Re: Date Parameter using different data types
            Stephane Marx

            Hi Alec,

            And I'm not sure that for your use case the parameter option is the best/ most convenient one.

            With a dummy dataset similar to yours:

            Month
            201701
            201702
            201703
            201704
            201705
            201706
            201707
            201708
            201709
            201710
            201711
            201712

            Here are the approaches:

            You can split your month field to isolate the month part (last 2 figures). And then create either a parameter or directly edit the aliases of this field.

             

            Start by creating a calculated field for the month part:

            RIGHT(str([Month]),2)

             

            Option 1:

            Right click the newly created field and select 'aliases'

            > you can assign the month names to the month number

            > you can then use this field as a filter or in your worksheet columns/rows

             

            Option 2:

            a. Right click the newly created field > create > parameter > choose a parameter type as string > select list > type the month name in your parameter

            b. create a calc field based on this parameter with If/ elseif statement (like you were doing) to have a dynamic field

             

            Hope this helps,

            1 of 1 people found this helpful
            • 3. Re: Date Parameter using different data types
              Joe Oppelt

              I would certainly be breaking the year portion out.


              Otherwise, they'll just be expanding the whole thing when 2018 arrives.