9 Replies Latest reply on Aug 5, 2014 10:58 AM by Rekha Bathla

    How to set default value of Date Parameter  based on calculation?

    Rekha Bathla

      Hi,

      I have created date Parameter Launch start date and end date (calendar display) in report.
      I have a requirement to set default Launch Start date as Current Month's start date and Launch End date as Current Month's End date.
      so that whenever user opens the workbook he/she can see by default current month's data and later user can select any date from calendar to change the view (flexibility to spacn across all time range.
      I didn't find an option to set current value based on calculated field( I created calculated fields -Month Start Date and Month End Date )

      Attaching the twbx file.
      Please suggest if there is work around to set default date of date parameter based on calculation or there is any other way to achieve this functionality

       

      Thanks,
      Rekha Bathla

        • 1. Re: How to set default value of Date Parameter  based on calculation?
          Shawn Wallwork

          Don't use a parameter, use a calculated date quick filter based on TODAY(). You can write it so it will always update like you described. Parameters don't allow for this kind of updating.

           

          Cheers,

           

          --Shawn

          • 2. Re: How to set default value of Date Parameter  based on calculation?
            Raza Sheikh

            Hi,

             

            You cannot set default value for a date parameter to be dynamic.

             

            I had re-created your workbook to show you an option that you might wanted to go for.

             

            Let me know if this helps you.

             

            Thank you

             

            NOTE: you need to upgrade your desktop to 8.2 to open my attached version...

            • 3. Re: How to set default value of Date Parameter  based on calculation?
              Joe Oppelt

              Shawn, Raza -- If I wanted to give the user a slider to select a month, and didn't want them to be able to select a month that is beyond the data avaliable to them (for instance, since it is August now, I don't want to let them select Sept 2014), can you think of a way in the month filter that Raza provided that would let me limit the max month the user can select?

               

              Or, in the example provided by Raza, the user can select "Next Month" in the filter.  And there happens to be September data, so that's a meaningful choice in this example.  But if there hadn't been September data, how could we prevent the user from selecting "Next Month"?  (Or, if I have a slider instead, how can I limit the max on the slider to "this Month"?)

              • 4. Re: How to set default value of Date Parameter  based on calculation?
                Shawn Wallwork

                Joe, I might be misunderstanding your question, but if you get rid of the parameters and just put the date field on the filter shelf and select Range of dates (instead of Relative dates) you'll get this:

                 

                range.png

                Tableau automatically defaults your range to min/max dates in the dataset. If you don't want Null values showing up, click Special and then non-null values.

                 

                Is this what you were asking?

                 

                --Shawn

                 

                EDIT: (Working off of Raza's workbook)you can also of course limit dates dynamically using calculated fields on the filter shelf (See attached). For instance:

                 

                IF [Launch Date]<=TODAY() THEN [Launch Date] END

                 

                When you put this on the filter shelf the last day of the Range filter will be 8/5/2014 (today). If you wanted the whole month you could use something like this:

                 

                IF month([Launch Date])<=month(TODAY()) THEN [Launch Date] END

                 

                Then you'll get the end date out to either the last day of the month or the last day of your data.

                • 5. Re: How to set default value of Date Parameter  based on calculation?
                  Noah Salvaterra

                  The first day of the current month is:

                  datetrunc('day',Today())

                  the last day of the current month would be

                  dateadd('day', -1, dateadd('month', 1, datetrunc('day',Today()))

                   

                  You could setup a parameter with a list on months included in the data (as strings) and add 'default' or 'current' to this list. Then create a calculation that includes a case or nested if to sort these out:

                  Case [Date Selection]

                  When 'Default' then Today()

                  Else date([Date Selection])

                  End

                   

                  Truncating this calculation as above would give you the first and last day of the selected month which could be used to build a filter. Just be sure to select default before uploading to server and don't allow users to save over the original.

                   

                  N.

                  • 6. Re: How to set default value of Date Parameter  based on calculation?
                    Joe Oppelt

                    Um...  yes.

                     

                    This is the second time today I've missed the obvious.

                     

                    I was looking at the situation with a "parameter" mindset.  I have a workbook I adopted.  The month-to-choose is controlled by a paramater, and to set the max on that parameter that the user can select, it has to be done manually because of the lack of dynamic parameter manipulation.

                     

                    As simple as that, I can replace the calc field that uses the parameter, and just put the date field in the filter.  Select on MY of the date.  Make the filter a slider.  In two minutes the parameter and calc field are now obsolete, and the sheet still works as designed.

                     

                    So that answers my question.


                    However, as I typed this response, I realized why the original design uses a paramater.  The date selected applies to multiple sheets, and those sheets are based on different data sources.  So I can't apply the new filter to all sheets...

                     

                    So for one sheet, the suggestion works.  I think I'm still stuck with the parameter though.

                     

                    I don't want to hijack some else's thread any more.  I saw Rekha's question and thought it might apply to my problem of having to do a monthly parameter update...

                    • 7. Re: How to set default value of Date Parameter  based on calculation?
                      Shawn Wallwork

                      I missed the multi-sheet, different data source aspect, so my bad too.

                       

                      --Shawn

                      • 8. Re: How to set default value of Date Parameter  based on calculation?
                        Joe Oppelt

                        No, not your bad at all.  I never mentioned it.  I didn't recognize the reason for needing the parameter (once I tried your suggestion) until I started looking at how to implement it in my actual application.

                         

                        I'll still be able to keep your suggestion in my bag of tricks for future applications.

                        • 9. Re: How to set default value of Date Parameter  based on calculation?
                          Rekha Bathla

                          Thanks everyone for all the suggestions !!

                          Raza Sheikh:

                          Requirement is specifically  to have  drop down as calendar display (default view as current month start and end date , with flexibilty to change &  select previous dates and future dates as well).

                          Solution you provided is close to requirements but giving selectors based on month and year (next & previous) etc. .

                          With Quick filter, we  get calender only when filter is based on Range of dates, again in that case there is no option to set default value 

                           

                          Thanks,

                          Rekha Bathla