{"id":900,"date":"2017-06-09T07:00:50","date_gmt":"2017-06-09T14:00:50","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=900"},"modified":"2017-06-01T11:56:01","modified_gmt":"2017-06-01T18:56:01","slug":"tableau-custom-date-range-picker","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2017\/06\/tableau-custom-date-range-picker\/","title":{"rendered":"Tableau: Custom Date Range Picker"},"content":{"rendered":"

Looking for a way to add custom date ranges to your worksheet or dashboard? For requirements like \"Last 7 Days\", \"Previous\u00a030 Days\", and \"Last 60 Days\" this is your ticket. (With some modifications \"Last Quarter\" and \"Prior Year\" are also possible). As a Tableau amateur I was having a tough time wrapping my head around this one. A little Google-Fu brought me to the post \"Custom date ranges using Parameter Controls<\/a>\" which had just enough information to get me started.<\/p>\n

\"\"<\/p>\n

It turns out one parameter and one calculated field are all you need. Check it out:<\/p>\n

Create The Parameter<\/h2>\n
    \n
  1. Create a new parameter and name it \u201cDate Range\u201d<\/li>\n
  2. Set the Data type to \u201cString\u201d<\/li>\n
  3. Set allowable values to \u201cList\u201d<\/li>\n
  4. Add the following items:
    \nLast 7 Days
    \nLast 14 Days
    \nLast 30 Days
    \nLast 60 Days<\/li>\n
  5. Click \u201cOK\u201d.\"\"<\/li>\n
  6. Right click on the \u201cDate Range\u201d parameter and select \u201cShow Parameter Control\u201d.\"\"<\/li>\n<\/ol>\n

    \u00a0<\/em><\/p>\n

    Create The Calculated Field<\/h2>\n
      \n
    1. Create a new calculated field and name it \u201cDate Range Picker\u201d.<\/li>\n
    2. Add the following code to the calculated field:<\/li>\n<\/ol>\n
       IF [Date Range]='Last 7 Days'\r\n\u00a0\u00a0\u00a0 AND [Score Dt]>=TODAY()-7\r\n\u00a0\u00a0\u00a0 AND [Score Dt]<=TODAY()\r\n\u00a0\u00a0\u00a0 THEN [Score Dt]\r\nELSEIF\r\n\u00a0\u00a0\u00a0 [Date Range]='Last 14 Days'\r\n\u00a0\u00a0\u00a0 AND [Score Dt]>=TODAY()-14\r\n\u00a0\u00a0\u00a0 AND [Score Dt]<=TODAY()\r\n\u00a0\u00a0\u00a0 THEN [Score Dt]\r\nELSEIF\r\n\u00a0\u00a0\u00a0 [Date Range]='Last 30 Days'\r\n\u00a0\u00a0\u00a0 AND [Score Dt]>=TODAY()-30\r\n\u00a0\u00a0\u00a0 AND [Score Dt]<=TODAY()\r\n\u00a0\u00a0\u00a0 THEN [Score Dt]\r\nELSEIF\r\n\u00a0\u00a0\u00a0 [Date Range]='Last 60 Days'\r\n\u00a0\u00a0\u00a0 AND [Score Dt]>=TODAY()-60\r\n\u00a0\u00a0\u00a0 AND [Score Dt]<=TODAY()\r\n\u00a0\u00a0\u00a0 THEN [Score Dt]\r\nEND\r\n<\/pre>\n
        \n
      1. Replace [Score Dt] with the date dimension from your data source.<\/li>\n
      2. Click \u201cOK\u201d\"\"<\/li>\n<\/ol>\n
         <\/p>\n
        Add The Filter<\/h3>\n
          \n
        1. Drag the calculated field \u201cDate Range Picker\u201d into your Filters.\"\"<\/li>\n
        2. The Filter Field dialog box will appear. Select \u201cRange of Dates\u201d.<\/li>\n
        3. Click \u201cNext\u201d.\"\"<\/li>\n
        4. Select \u201cSpecial\u201d from the Filter dialog box.\"\"<\/li>\n
        5. Select \u201cNon-null dates\u201d.<\/li>\n
        6. Click \u201cOK\u201d\"\"<\/li>\n<\/ol>\n
          That's it! You're a hero!<\/p>\n","protected":false},"excerpt":{"rendered":"
          For Tableau requirements like: Allow user to choose \"Last 7 Days\", \"Last 30 Days\", etc. this is your ticket. It turns out one parameter and one calculated field are all you need.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,26,193],"tags":[20,25,195,194,5],"class_list":["post-900","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-charts-and-graphs","category-tableau","tag-business-intelligence","tag-chart","tag-filter","tag-tableau","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/900","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=900"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/900\/revisions"}],"predecessor-version":[{"id":916,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/900\/revisions\/916"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=900"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=900"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=900"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}