7 Replies Latest reply on Jan 19, 2016 5:02 AM by Andrew Watson

    Month to Date

    Randy Allan

      In the attached workbook, I have some data on a graph from 2015 and 2016. I have a [Months to Date] filter applied where it will show any data point from 2015 up to whatever the current month it is. When I remove the filter, obviously it will show any data point in 2016 then. The filter is a true/false and I want to be able to have this on a dashboard as a quick filter, but call it "Forecasting". So the user can essentially select a radio button if they want to "forecast" the remainder of the year which will show all data points as mentioned above.


      (I know, Tableau has a built in forecasting function but for some reason it is greyed out for me.)

        • 1. Re: Month to Date
          Russ Lyman

          I have attached an example for you.


          To add your quick filter to the dashboard, you just need to create a new dashboard. You can select the single value list option to get radio buttons.


          Also, the forecast option was grayed out because you were not using a continuous date format, and the table calculation you were using didn't support forecasting.


          I have included both a forecast example and the radio button filter in the attached file.


          Let me know if you have questions.

          • 2. Re: Month to Date
            Randy Allan

            When I look at Dashbaord 1 in your example, instead of having options that say (All), False, and True, can the options just say "True" and "Forecast"? "True" would act what True currently does and Forecast would act as what (All) currently does.

            • 3. Re: Month to Date
              Andrew Watson

              You could alter the calculated field 'Months to Date' to be:


              iif([Opened]<= TODAY(), "True", "Forecast")


              Also hide the All on the quick filter in the 'Customize' section:


              • 4. Re: Month to Date
                Randy Allan

                I set the calculated field to iif([Opened]<= TODAY(), "True", "Forecast").


                When I select Forecast it removes the data from 2015. If I select (All) it keeps it. How can I keep the 2015 data if I select Forecast?

                • 5. Re: Month to Date
                  Andrew Watson

                  Filtering won't work for this, parameters will have to be used instead - unless you're happy keeping the 'All' and losing the Forecast of course, in which case filtering is fine.


                  Create a parameter called 'Selection' with the values True and Forecast.


                  Alter the calculated field 'Months to Date' to be 'IF [Opened]<= TODAY() then "True" end'


                  Create a new calculated field called Filter:


                  IF [Selection] = "Forecast" THEN 'True'

                  ELSEIF [Selection] = [Months to Date] THEN 'True'

                  ELSE 'False'



                  Selecting Forecast in the parameter actually selects all dates. Selecting True selects only those months with a value of True, set in the Months to Date calculated field.


                  I've attached the twbx.



                  • 6. Re: Month to Date
                    Randy Allan

                    Thanks that looks like it will work great. Is it possible to have the Mark type to be a dotted line for any future months?

                    • 7. Re: Month to Date
                      Andrew Watson

                      It's possible to show as a different colour but I'm not sure about dotted line - this I complicated. If you want to explore dotted lines further you can check this out: Eddie Van Halen and Dashed Lines | Drawing with Numbers


                      To do a different colour it would take a slightly different approach than we looked at previously but is significantly easier than a dotted line. Based on my understanding you want:


                      1. Anything 'actual' - i.e. before today - to be displayed always
                      2. Anything 'forecast' to be displayed when the user wants to see it


                      In which case you can produce a calculated field to show 'actual', a calculated field such as IF [date] <= today() THEN [Measure] END.


                      Create a very similar calculated field to show 'forecast', such as IF [date] > today() THEN [Measure] END.


                      Put these on to a dual axis line chart and Synchronise the axes.


                      You should now see the Actual and Forecast appearing like a line chart, with a break to split the Actual and Forecast.


                      Next is to show or hide the Forecast depending on the user choice. Create a new calculated field, ShowForecast, IF [Selection] = 'Forecast' THEN [Forecast] END. [Selection] is the parameter with values of Forecast or Actual.


                      Put ShowForecast in the place of the Forecast pill on the chart and now you can switch the forecast on and off plus easily differentiate which is forecast and which is actual.


                      Attached is a workbook I quickly threw together to show you how it works.