2 Replies Latest reply on Nov 21, 2017 9:16 AM by daphne liu

    How to link the date prompts report display,date type,calender and range of dates in tableau as provided in given screenshot?

    sana.firdaus

      I have a report to create based on the below requirements.User will have the option to select from date type ,then calender ,then report display and based on the selection the "from " and "To " will display.for ex-if user selects Date type as "Order date" and then under calender selects Company Calender and in report display selects monthly then the from month and to month will show in mm/YYYY format and as per company calender and date type.

      Is there any work around for it?

        • 1. Re: How to link the date prompts report display,date type,calender and range of dates in tableau as provided in given screenshot?
          Glenn Kuly

          Hi Sana, I did something like this for a past project, and I can share a workbook (based on the Superstore dataset) showing one approach you could using that makes use of parameters.

           

          Check out the following worksheet, which allows viewers to look at data either in months or years:

           

          data parameter.png

           

          The parameter to choose date level looks like this (you can add as many levels as you want):

           

          date level.png

           

          For each level available in the date level parameter, there is a an actual date selection parameter:

           

          date level-month.png

           

          For the measure to be shown, you will need a calculated field along these lines:

           

               CASE [Select Date Level]

               WHEN "Year" THEN [Sales by Year]

               WHEN "Month" THEN [Sales by Month]

               END

           

          Then for each date level, you will need another calculated field, months looks like this:

           

               IF DATETRUNC('month',[Order Date]) = [Order Date (Months) Parameter] Then [Sales] ELSE 0 END

           

          I've set up my date selector using discrete years and months, but you can do something similar for ranges of dates. You will need start date and end date parameters for each date level, and then need to use calculated fields that look something like this:

           

               IF DATETRUNC('month',[Order Date]) >= [Start Date Month] AND DATETRUNC('month',[Order Date]) <=  [End Date Month]) THEN [Sales] END

           

          (I've adapted this calculated field from another workbook, and haven't tested it out in the above example.) Order is an actual date field, while [Start Date Month] and [End Date Month] are parameters. My workbook is attached (v10.4).

           

          I notice that you have two different date types and two different calendars in your requirements (and four date levels). You've got boatload of work to do setting up calculate fields, good luck!

           

          -- Glenn

          • 2. Re: How to link the date prompts report display,date type,calender and range of dates in tableau as provided in given screenshot?
            daphne liu

            Done something similar. Using 2 parameters 'Last No. Month' and 'Report End Week/Month Date'. You can set 'Last No. Month' to 1,2,3...but when you set it to 12, your charts change to trend analysis. See my workbook example at Tableau Public

            Good luck!!