4 Replies Latest reply on Jan 21, 2013 12:51 AM by Tore Levinsen

    dynamic title based on filtering

    Tore Levinsen

      Hi,

       

      in my report (using Norwegian formatting on the dates), the user can filter on Period (Day, Week, Month) - which then select the underlying data based on this selection. If they select Day, data marked "D" in the source table will be shown. This is done because the data is not possible to summarize day to day (or week/month).

       

      Problem 1.jpg

       

      Then there is a new filter where you should be able to select a relative date - i.e. "Today", "Yesterday", "next three days". When selecting the last one, there will then be 3 data points for each dimension, one for each day. This works ok as long as I've put "Day(workdate)" as a column.

       

      But when they change to Week (or Month), the axis will still show the "Day(workdate)" (start of that week) underneath (and also in the title), I would like for the axis to show Weeknumber when selecting a week, even in the Title under "Period shown". When selecting "This week" it will show 7. jan, for "Next 2 weeks" it will say in the title: "Period shown: 07.01.2013 to 14.01.2013" -> which is just the first date of this week and the first date of next week. It should've said 07.01.2013 to 20.01.2013 or "week 1 to week 2 2013".

       

      Problem 3.jpg

      This should state: "Week 1", "Week 2" (if selecting Month, "January", "February" etc)

       

      Is this possible to do?

       

      Also, when changing from Day to Week etc, is it possible to automatically in filter 2 ("Filter periods:") to jump to Week selection?

      Problem 2.jpg

      This should have "Weeks" automatically selected.

       

      Thanks for any input!

       

      Tore Levinsen

        • 1. Re: dynamic title based on filtering
          Nathan Krisanski

          Hi Tore,

           

          You can do this by using the Parameter feature of Tableau.

           

          Instead of using data filters for the selection of Day, Week, Month, create a parameter called "DateTypeSelection" and give it the three allowed values and set the Value to match the source field in your data and Display As field what you want to user to see. You can leave your "Filter periods" filter in place.

          ParameterScreenShot.jpg

          Now add your date type field to the filters and choose the Condition Tab along the top. Select By Formula and enter the formula [datetypefield]=[DateTypeSelection]. This will now filter your data based on the value of the parameter.

           

          But that is just the same as what you had before right...

           

          So now the cool part. Create a calculated field called DateTypeLabel. This field will be a simple if elseif end function that will return the date you want to format based on what is chosen.

          eg.

          if [DateTypeSelection] = "D" then

          //User has selected day, so this field will show the Day(workdate)

          str(day([workdate]))+" "+left(datename("month",[weekdate]),3) // returns day and 3 letter month eg. 3 Mar

          elseif [DateTypeSelection] = "W" then

          //User has selected week so show weeknumber

          "Week "+str(datepart("week",[workdate]))

          elseif [DateTypeSelection] = "M" then

          //User has selected month so show month abbreviation

          left(datename("month",[weekdate]),3) //returns Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

          end

           

          Now add this calculated field to your columns. Because we made it a string value and not a date anymore, it will sort them alphabetically. Not what we want. So sort the DateTypeLabel field by [weekdate] Minimum. This will ensure they are always in the correct order on your columns.

           

          So just add your parameter card to your dashboard or worksheet and watch the columns change and data update as you select Day, Week or Month.

          • 2. Re: dynamic title based on filtering
            Tore Levinsen

            Thanks, that helped with my first problem with selecting the Date type!

             

            For the second part of the problem, when selecting Month instead of Day - I have to manually change the filter from Day to Month, as in my third picture display (see original post). Is this possible to "fix" as well?

             

            Thanks again!

             

            br

             

            Tore

            • 3. Re: dynamic title based on filtering
              Nathan Krisanski

              Hi,

               

              You cannot set the period type for a relative date filter automatically.

               

              So you have to decide between using the relative date filter dialog (as you currently have) where you have a nice control for changing between date types, or use a calculated field than can be automatic.

               

              For option 2, create a new parameter called DateTypePeriod (or similar). This will be an integer and will indicate the number of days,weeks,months to filter either side of today. So to replicate the idea of last 3 days, this parameter would be -3. Next 2 weeks, it would be 2. Just today, 0.

              So create the integer parameter and setup the limits if you want to limit how may columns.

               

              The create a new calculated field to act as our filter. It will result in two values, Show or Hide. Add this to the filter shelf and select Show. The format will be similar to our Label param above (these will include today always):

              if [DateTypePeriod] = 0 and [weekdate] = Today()

              then "Show"

              else

              if [DateTypeSelection] = "D" then

                  if [DateTypePeriod] < 0 and [weekdate] >= dateadd("day",[DateTypePeriod],Today()) and [weekdate] <= Today() then "Show"

                  elseif [DateTypePeriod] > 0 and [weekdate] <= dateadd("day",[DateTypePeriod],Today()) and [weekdate] >= Today() then "Show"

                  else "Hide" end

              elseif [DateTypeSelection] = "W" then

                  if [DateTypePeriod] < 0 and [weekdate] >= dateadd("week",[DateTypePeriod],Today()) and [weekdate] <= Today() then "Show"

                  elseif [DateTypePeriod] > 0 and [weekdate] <= dateadd("week",[DateTypePeriod],Today()) and [weekdate] >= Today() then "Show"

                  else "Hide" end

              elseif [DateTypeSelection] = "M" then

                  if [DateTypePeriod] < 0 and [weekdate] >= dateadd("month",[DateTypePeriod],Today()) and [weekdate] <= Today() then "Show"

                  elseif [DateTypePeriod] > 0 and [weekdate] <= dateadd("month",[DateTypePeriod],Today()) and [weekdate] >= Today() then "Show"

                  else "Hide" end
              end

              end

               

              So add the parameter for the "Period" selector to under your Date Type parameter and you will have a user interface for selecting a date type and relative period based on that selection. I would do a slider for this period selector so that users can slide their period selection.

              I didn't include this in the original answer as I actually think the relative date dialog as standard is more user friendly in this case. It also gives the user more control over what they are seeing. Why can't they chose to view results by day for the last week? or month?

              Up to you, but the workaround is there if you want it.

              1 of 1 people found this helpful
              • 4. Re: dynamic title based on filtering
                Tore Levinsen

                Hi and thanks.

                 

                I agree that the existing/built-in solution would be better, it would just be a matter of teaching the users how to use the report - so I'll stick with that for now.


                But thanks anyway for the workaround, it might be useful at a later stage.

                 

                t