1 Reply Latest reply on Nov 13, 2018 4:49 AM by Rahul Kohli

    How to have a custom date filter that includes half an hourly filter with date/time names rather than numbers

    Rahul Kohli

      Hi Everyone,

                            I think many tableau users will find it very useful, I had a requirement where i had to give a custom date filter for anyone viewing my tableau workbook, in other words, user without having access to Tableau developer should be able to display data in the following format as per their choice : -

      1. Yearly - 2018 , 2017 etc...
      2. Quarterly- 1 , 2 , 3 ,4
      3. Monthly - Jan , feb , Mar....
      4. weekly - 10-21-2018 to 10-27-2018
      5. daily - 1,2,... 31
      6. hourly - 00:00, 01:00 .... 23:00
      7. weekday - Monday, Tuesday ....
      8. half houry - 00:00, 00:30 , 01:00 ....

      see figure below...

       

      I tried finding code everywhere but wasn't able to do so, finally, I cracked it myself so posting it below for other users who wants to implement it in their workbooks

        • 1. Re: How to have a custom date filter that includes half an hourly filter with date/time names rather than numbers
          Rahul Kohli

          Steps : I created three parameters and displayed them on workbook: -

          Start date - see screenshot

          End date - same as start date

          Select date aggregation - see screenshot

          see screenshots : -

           

          Step 2:

          Create a Calculated field ( i named it "zselected date aggregation") - prefixing Z will have all your custom fields at the bottom of the list, it makes them easier to find.

          enter the following code into "zselected date aggregation" , in the code 'datecreated' is my date variable replace it with yours.

           

          CASE [Select Date Aggregation]

          WHEN "Yearly" THEN DATENAME('year',[datecreated])

          WHEN "Quarterly" THEN DATENAME('quarter',[datecreated])

          WHEN "Monthly" THEN DATENAME('month',[datecreated])

          WHEN "Weekly" THEN STR(DATE(DATETRUNC('week', [datecreated])))+ " to " +

                             STR(DATE(DATETRUNC('week', DATEADD('week', 1, [datecreated]))) -1)

          WHEN "Daily" THEN DATENAME('day',[datecreated])

          WHEN "Weekday" THEN DATENAME('weekday',[datecreated])

          WHEN "Hourly" THEN DATENAME('hour',[datecreated])+ ":00"

          WHEN "Half Hourly" THEN (str(datepart('hour',[datecreated]))+":"+

                                   right("0" +

                                  str(int(datepart('minute',[datecreated])/30)*30),2))

           

           

          END

           

          Step 3: create a second calculated field ( i named it "zDate range")  , enter the following code in it (replace 'datecreated' with your date variable)

           

          [datecreated]>= [Start Date] and [datecreated]<= [End Date]

           

          Step 4: drag "zselected date aggregation" capsule in columns or rows field as per your requirement.

           

          Done.

           

          If this helped you , please feel free to send me a thanks