1 2 Previous Next 22 Replies Latest reply on Oct 4, 2018 2:46 PM by priti.shah.0 Go to original post
      • 15. Re: Create dynamic parameter based on data fields or calculations
        Joe Oppelt

        Priti -- If my understanding that I asked in the previous reply is correct, you can do away with the parameter altogether.  You can even do away with reformatting the string field.

         

        In the attached I've done a series of things. First look at Sheet 2.

         

        I created a [Period Selection String] calc that creates the string format you are looking for, and it works off the actual date field.  The DATENAME function pulls the year and month value out of the date field for you and returns the values as a string.  I displayed that on Sheet 2.

         

        If we are doing a year-to-date total, you can do that with a RUNNING_SUM function.  On Sheet 3 I set that up.  (No filtering yet.)  You can see that the values add up along the year.  Right click on the Yearly Running calc on the text shelf.  Select "Edit Table Calc".  You will see this:

         


        by default Tableau will put this calc on the sheet using table(across).  That means it will start in the first cell and just keep adding values until the end.  But I want this to go back to zero with every year.  So I clicked "Specific Dimensions" and selected both Year and Month.  This tells Tableau to cycle through the data year by year, and within years, month by month.  And the key is also to select a RESTART dimension so that it goes back to zero with every year.


        You can see the results on Sheet 3.

         

        Next, go to Sheet 3(2).  Here I put a filter to display only one month from the previous sheet.  I use a table calc as the filter.  (LOOKUP is a table calc.)  When we use a table calc as a filter, we do not filter out rows from the underlying table like a standard filter does.  A table calc filter controls what part of the existing table gets displayed.  So all the rows are still in the table and the running_sum calc still arrives at the correct value on March 2017 (which is what I selected.)  You can see that it's the same number as you have in sheet 1.


        But look at the sort order of the values in the filter.  Because this is a string field, it's sorting alphabetically.  You probably don't want that if you will be letting a user select a value.

         

        Go to Sheet 3(3).

         

        Here I made another selection field that is actually a date.  (It's just a copy of the [End Date] field.  And I made a different table calc filter that uses the date field.  And I formatted that field to show only month and year.

         

         

         

        I selected this format:

         

        Now, because it's a date field, Tableau sorts the filter in date order.  And you get what you see on Sheet 3(3).

         

        So in the end we can actually do away with all the [Period Selection...] fields, and just shove [End Date] into the [Just show one month...] calc, and just format it with the selection I used.


        See attached.

        • 16. Re: Create dynamic parameter based on data fields or calculations
          priti.shah.0

          Hi Joe,

          Thanks a lot for a great solution for the Dates! Thank you for your time you are a true Tableau Support!

          My Financial Dashboard is complicated. I'm sharing the screen shot.

          In the screen first 4 columns are calculation for Month to date and rest of the column Year to date

          I have tons of calculation using the parameter Month End Date.

          mostly I'm comparing the month and year.

          • Can I use the calculation you provided instead of the parameter in my calculations?
          • then provide user a year and the calculated field for the period selection?

           

          ex of calculation:

          IF     

              DATEDIFF('year',[END_DATE],[p.Month End Date])=0

              AND

              MONTH([END_DATE])= MONTH([p.Month End Date])

          THEN

              [Actual Period Net Chg - Exp]

          END

           

          I truly appreciate your support and care of other Tableau developers. I also need a mentor like you to deliver the perfect dashboards.

          This kind of support I never got from Tableau.

          I'm also willing to WebEx and show my work for your guidance if it is appropriate.

          Thanks.

          -Priti

          • 17. Re: Create dynamic parameter based on data fields or calculations
            Joe Oppelt

            Priti Shah wrote:

             

            ...

            In the screen first 4 columns are calculation for Month to date and rest of the column Year to date

            I have tons of calculation using the parameter Month End Date.

            mostly I'm comparing the month and year.

            • Can I use the calculation you provided instead of the parameter in my calculations?
            • then provide user a year and the calculated field for the period selection?

             

            ex of calculation:

            IF

            DATEDIFF('year',[END_DATE],[p.Month End Date])=0

            AND

            MONTH([END_DATE])= MONTH([p.Month End Date])

            THEN

            [Actual Period Net Chg - Exp]

            END

             

            ...

            I would need to see your workbook to understand how you are using the parameter now, and how your approach could be modified to use what I did.

             

            For instance, the example workbook just had [End Date].  If you are doing month to date stuff, I would have to assume you have daily data, and another date field with daily date values.  If so, you can have a table calc that restarts every [End Date], which would give you monthly values.


            But if you also have multiple sheets on a dashboard, and you want one selection control to govern the dates on all of them, we can't use the LOOKUP filter that I made.  A table calc is specific to the sheet it is on.  We would have to go back to a parameter-driven approach.

            • 18. Re: Create dynamic parameter based on data fields or calculations
              priti.shah.0

              Thanks Joe.

              I have 16 dashboards for various visualization and each dashboards have multiple worksheets.

              I'm using the Month End Parameter control to  govern the dates on all of them.

              Is it ok for you to WebEx to look at the worksheets I have? unfortunately based on company's rule I can't publish the workbook.

              This solution will also help me in my next project. Again, thank you so much for help!

              • 19. Re: Create dynamic parameter based on data fields or calculations
                Joe Oppelt

                Knowing that you have multiple sheets (and dashboards) is all I really need to know.  You can't use the LOOKUP method I used for that.  You'll need to be parameter driven after all.

                 

                Here is a problem with parameters:  As of now (even Tableau V2018.2) we don't have the concept of dynamic parameters.  As your data grows, your parameter list won't grow dynamically with it.  I have situations like that, and I have to go in and add values monthly to keep the parameter list in sync with the available data.

                 

                You could change your approach to have two parameters:  One for year, and one for month.  Or even just use one parameter where the user selects a date off a calendar.  That one would never have to be updated manually.  For your purposes it doesn't matter what day of the month the user clicks.  You're really only concerned about the month and year they click in.

                 

                In the attached see [Parameter 2].  I displayed it on Sheet 1.  Click in the field, and you'll get a calendar that you can flip through.  (And it's not the prettiest thing.  Even though there is a "display format" in a date parameter, and even though I selected "Month,YYYY", it really only displays as MM/Dd/YYYY on the sheet.  (Disappointing.)

                 

                So we're almost back to the original question you had.  Now that I've been playing with this a bit, I think your real question is how you can populate that parameter list quickly and easily.  You can get all the actual dates in there quickly enough by selecting the End Date as the feed for the internal values, but how do you populate the "Display As" values easily.  Is that what you are looking to figure out?

                • 20. Re: Create dynamic parameter based on data fields or calculations
                  priti.shah.0

                  So back to the solution I'm looking for:

                  The user can select a year based on the year.

                  month end date parameter box should populate. but only month should populate which has a status closed for that year.

                   

                  Sorry for the confusion.

                  • 21. Re: Create dynamic parameter based on data fields or calculations
                    Joe Oppelt

                    What you're looking for is a dynamic parameter, which we don't have now.

                     

                    Parameter lists are fixed lists, populated at the time the parameter is created or edited in Desktop.

                    • 22. Re: Create dynamic parameter based on data fields or calculations
                      priti.shah.0

                      Thank You Joe.

                      Thank you for your support!

                      -Priti

                      1 2 Previous Next