10 Replies Latest reply on Mar 3, 2015 8:14 AM by James Lapp

    How to make a parameter local to one sheet?

    James Lapp

      Hello,

       

      I have two parameters. The parameters establish thresholds ($ amount and %) for period over period changes in my workbook. When the changes are greater than the parameters, the parameters trigger a calculated field to highlight that row.

       

      The challenge I have is that each of my companies (a dimension in my dataset) has different thresholds. Instead of creating a bunch of different parameters for each company, I am thinking of creating a separate sheet for each company (with the others filtered out) and having the parameter set to different values on each sheet.

       

      But if I change the parameter on one sheet, it changes on all. Is there a way to have the same parameter set to different values on different sheets?

       

      Sample workbook attached. For example, on the Tbl NiMO tab, I want the Threshold - $ set to $500,000. On the Tbl BG tab, I want it set to $50,000.

        • 1. Re: How to make a parameter local to one sheet?
          Joe Oppelt

          Maybe this is an off-the-wall idea, but what about doing a little driver file -- excel perhaps -- that has all your companies in one column, dollar thresholds in a second and percentage thresholds in a third.

           

          Blend on company.  Use the respective values in your calcs.

           

          Parameters are global across the workbook.

          1 of 1 people found this helpful
          • 2. Re: How to make a parameter local to one sheet?
            Yuriy Fal

            Hi all,

             

            One could implement the logic proposed by Joe

            via row-level calc right it the workbook.

             

            Please find the wb attached.

             

            A row-level measure calc is [Treshold -- $].

            A modification is made to [Highlight - Threshold %]

            to replace a reference to a param [Threshold - $].

             

            Yours,

            Yuri

            • 3. Re: How to make a parameter local to one sheet?
              Matt Lutton

              Is there a way to have the same parameter set to different values on different sheets?

               

              No, not as far as I know.  Parameters are considered to be similar to a "data source" across the entire workbook, independent of your data connections.  I know of no way to make them local to a single sheet.  As suggested, you might look to a different approach.

              • 4. Re: How to make a parameter local to one sheet?
                James Lapp

                Yuri - thank you. This is even a better solution than having a local parameter or parameter for each OpCo. Thanks so much!

                 

                Joe - that is a good idea and something I'll keep in mind in the future for other problems. I think Yuri's solutions gets me what I need for now without an additional data source. Thanks though!

                • 5. Re: How to make a parameter local to one sheet?
                  Joe Oppelt

                  Right, Matthew.  I treat a parameter as a single-value data source.

                  • 6. Re: How to make a parameter local to one sheet?
                    Joe Oppelt

                    Yes, Yuri, that accomplishes the same thing if the list of companies will not change, or if the thresholds will always remain static.

                     

                    The reason I suggested the extra data source is to create future flexibility for change.  With a data source approach the changes will never require someone to know where programmatic changes would be required.

                    • 7. Re: How to make a parameter local to one sheet?
                      James Lapp

                      Joe - that's a good point. Yuri's solution is good in that it's self-contained within Tableau but my analysis will require dynamisms.

                       

                      To follow-up on my original question and Yuri's solution, the thresholds I have will remain static on an annual basis. However, my workbook has parameters which allow the user to select unique periods to compare (in weekly units). So, I do need the thresholds to update based on the number of weeks selected.

                       

                      Here's what I tried but am running into the aggregate / non-aggregate mix error in my case statement.

                       

                      1. I created a calc field which counts the number of weeks the user selects (Period 2 End - Period 2 start +1). For it count correctly, this number needs to be viewed as an average (or a minimum).

                       

                      2. I then created a calc field which calculates the amount the threshold will need to be pro-rated based on number of weeks (avg(No of weeks) / 52).  Could use min as well.

                       

                      3. I created a calc field for each company with a threshold: Threshold $ (this is hardcoded #) * Pro-rate.

                       

                      4. I then used the same formula as Yuri substituting the hardcoded #'s with the calc fields from #3 but get the agg/dis-agg error.

                       

                      I understand this error and know why Tableau can't do this, but am struggling to find a work-around. Any advice? is there a way to calculated #1 (number of weeks) without using avg/min.

                       

                      Attached is updated workbook for reference.

                      • 8. Re: How to make a parameter local to one sheet?
                        Joe Oppelt

                        Yes.  [ThresholdPro-ration] uses MIN() so it's an aggregate.

                         

                        That carries up to [Threshold - $ (all OpCos)].

                         

                        [Receiving Company]  is not an aggregate, and Tableau chokes on that.

                         

                        Change the calc to this:

                         

                        case attr([Receiving Company])

                        when 'Niagara Mohawk Power Corp' then ([Threshold - NiMo])

                        else 0

                        end

                        1 of 1 people found this helpful
                        • 9. Re: How to make a parameter local to one sheet?
                          Joe Oppelt

                          the ATTR() function makes tableau "see" a non-aggregate as an aggregate.

                          1 of 1 people found this helpful
                          • 10. Re: How to make a parameter local to one sheet?
                            James Lapp

                            Joe - thanks so much. That's a great trick to add to my toolkit.

                             

                            Thanks to all for the help on this project. I'm off to a great start because of all of you. Thank you!