8 Replies Latest reply on Aug 2, 2018 7:35 AM by Michael Gillespie

    Calculated Field - Variable Value

    John LaPlante

      Hi all!

       

      I hope you can lend a hand to a new Tableau user!

       

      I would like to set a calculated field output based on a slider that the user would manipulate.  For example, here is my calculated field, with a name of "Program Used?"

       

      IF [usage] > variable THEN "Yes" ELSE "No" END

       

      So a user might choose a value of 0 on the slider, which would then store the value of 0 in the variable field.  This will then cause the "Program Used" field to be set to "Yes" for all values greater than zero.

       

      Or, a user might choose a value of 5, , which would then store the value of 0 in the variable field, whereby the "Program Used" field will be set to "Yes" only for rows where the usage column is greater than 5.

       

      Does this make sense? 

       

      I'm using "Program Used" on the Columns shelf to show the difference in average test scores for people who used a particular program vs those who didn't.  I can't use the "usage" field as a filter, because that then only shows part of the data.

       

      THOUGHT:  Might a LOD calculation also work?  I'm not too familiar with those.

       

      Any help is greatly appreciated!!

       

      John

        • 1. Re: Calculated Field - Variable Value
          Michael Gillespie

          John, if I'm following what you're asking, you need to create something to use for the slider value, right?

           

          We'd normally use a parameter for that function.  What are the bounds of the slider - zero to 10? 100?  1000?

           

          Then you create your calculated field.  I'd suggest using 0 and 1 for your values instead of Yes & No - numbers evaluate faster than text.

           

          If any of that doesn't make sense, ask more questions!

          • 2. Re: Calculated Field - Variable Value
            Jim Dehner

            Hi John

            sounds a little more complex than what is needed - you can simply use a filter on "Usage" - draf to the filter shelf and select all values

             

            then select range of values in the box that opens

             

            and show the filter - it will be a slider - and will filter out all values that do not fit in the range

             

            User direct input of a value is done through a parameter - they are single valued and are constant until changedd by the user - they need to backed up by a filter or used in a calculation - they don't do anything by themselves

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Calculated Field - Variable Value
              John LaPlante

              Hi Michael, that's not what I'm looking for.  Here's an example of my data set.

               

              UseProgram? is a calculated field:  = IF Usage > [Slider] THEN "Yes" ELSE "No" END

               

              StudentID          Usage          TestScore          UseProgram?

              1                         0                    10

              2                         0                    10

              3                         5                    20

              4                         10                  30

               

              UseProgram? is on the Columns Shelf, and AVG(TestScore) is on the Rows Shelf.   [Slider] is a filter with possible values of between 0 and 10, the range of valued in the Usage column.

               

              If slider is set to 0, then "UseProgram?" will be "No" for rows 1 and 2, and will be "Yes" for rows 3 and 4, The display will show an average value of 10 for the UseProgram? = No column and 25 for the UseProgram? = Yes column.

               

              If slider is set to 5, then "UseProgram?" will be "No" for rows 1, 2, and 3, and will be "Yes" for row 4.  The display will show an average of 13.33 for the "No" column, and 30 for the "Yes" column.

               

              Does this make sense?  I'm just trying to vary the cutoff between what counts as using the program for an analysis.

               

              John

              • 4. Re: Calculated Field - Variable Value
                John LaPlante

                Hey Jim, unfortunately, this doesn't work.  I'm not just displaying the usage that is being selected...I'm displaying the difference between 2 groups.  If I use the slider on usage I'm ignoring the data that makes up the other half of my comparison.  See my response above. 

                • 5. Re: Calculated Field - Variable Value
                  Michael Gillespie

                  OK, that is more complicated!

                   

                  First of all, you likely will need a FIXED LOD calculation for the 'Yes/No' values.  You want to determine that value at the Student ID level, no matter what else you're doing, right?  So, something like {FIXED [StudentID]: IF [Usage] > [Slider] THEN "Yes" ELSE "No" END}.  You'll have to play with the logic there: I suspect that won't work as written.

                   

                  A key concept in understanding what LOD's do.  Tableau does everything in reference to what's currently in the VIEW (i.e., on the worksheet).  In this case, you will NOT have StudentID in the view, so Tableau will not account for it in any calculations you do.  That's what an LOD does: it tells Tableau that, regardless of what's actually in the view, I want you to calculate this value in this exact way.  A FIXED LOD says: I don't care what's in the view, use these fields to do the math.  INCLUDE says, even if the field is not in the view, use it anyway; EXCLUDE says, even if the field IS in the view, do not use it.

                   

                  We still need a parameter to hold the slider values though: that's the "variable" in your calculation.  We'll use that parameter value to do the UseProgram? calculation. 

                   

                  Once that is done, then we need a calculation to figure out what we're including in the Average calculations.  That will take a little more thought to get right, but not a lot.  It will also need to be a FIXED LOD - we have to get the UseProgram? value for each Student ID to figure out the numerator and denominator for those calculations.

                   

                  Making sense?

                  • 6. Re: Calculated Field - Variable Value
                    John LaPlante

                    Hi Michael, yes...getting closer.  If there was just a way to dynamically change the value of a parameter...that would do the trick. 

                     

                     

                     

                    John

                    • 7. Re: Calculated Field - Variable Value
                      John LaPlante

                      Problem Solved!

                       

                      I created a parameter called "Usage", set it as a 2-step value from 0-20.

                       

                      Then I right-clicked on the filters box to the extreme right of my graph, and selected "Parameters" and "Usage".  It showed!

                       

                       

                      Then I changed my calculated field to use this parameter.

                       

                       

                      Now I can change the slider and it will update the calculated field.  Perfection!!

                       

                       

                      Voila!  Works like a charm.

                      1 of 1 people found this helpful
                      • 8. Re: Calculated Field - Variable Value
                        Michael Gillespie

                        Fantastic, John.  Glad it works.