2 Replies Latest reply on Jun 14, 2018 4:50 AM by Mavis Liu

    Data parameter for Date, week, MonthYear

    Roisha Pal

      I have a date column and I want to create parameter so that if user select MonthYear, dashboard should display in format JAN-2017, FEB -2017 AND IF user select Week they should see data as W1-2017, W2-2017 etc also for date as Full date.

        • 1. Re: Data parameter for Date, week, MonthYear
          Zhouyi Zhang

          Hi, Roisha

           

          Please find my solution attached. below is the result for your reference.

           

          Hope this helps

           

          ZZ

          • 2. Re: Data parameter for Date, week, MonthYear
            Mavis Liu

            Hi Roisha,

             

            Please make the parameter and call it Date Format:

            2018-06-14_12h50_02.png

             

            Then create this calculated field:

            Date Format Show

             

            IF [Date Format] = 1

            then upper(  left( str( DATENAME('month',[Order Date])),3)) + ' ' + right( str( DATEPART('year',[Order Date])),2)

            ELSEif [Date Format] = 2

            then

            'W' + str(DATEPART('week', [Order Date])) + ' ' +  right( str( DATEPART('year',[Order Date]))

            ,2)

            ELSE

            str(DATEPART('day',[Order Date])) +' ' + upper(  left( str( DATENAME('month',[Order Date])),3)) +' ' + right( str( DATEPART('year',[Order Date])),2)

            END

             

            then bring this field into columns. Please note that this is just string now, as we had to break up the dates to create the right label. So we will need to sort this field, right click on the field and select sort;

             

            2018-06-14_12h43_56.png

             

            Select asc order date minimum:

             

            2018-06-14_12h44_07.png

             

            Now your view should look like this:

             

            2018-06-14_12h46_31.png

             

            Please see attached workbook.

             

            Thanks,

             

            Mavis