1 2 Previous Next 15 Replies Latest reply on Mar 19, 2017 12:31 PM by Joshua Preston

    Parameter not working

    Joshua Preston

      I have a simple TB10 workbook (attached) with a simple CASE statement in a calc field and accompanying parameter, but I can't get it to work. Am I missing a step?

       

        • 1. Re: Parameter not working
          Wes Reneau

          Hey Joshua,

           

          It looks like you are almost there. You need to drag the calculated field Levels Parameter Calc Field to the Filters shelf. I tested it and it seemed to work, but you may need to adjust your calcuated field to handle Nulls differently.

          • 2. Re: Parameter not working
            Joshua Preston

            Thanks Wes. I have the calc field in the filters shelf now, but this is where I get stumped. You mentioned Nulls, but I have none (I can create a dummy Null).

             

            Can you walk me through how to get the filter to display more than this?

             

            • 3. Re: Parameter not working
              Wes Reneau

              I have a lunch meeting right now, but if someone else hasn't helped by the time i return i'll take a look.

              • 4. Re: Parameter not working
                Joshua Preston

                Thanks Wes!

                • 5. Re: Parameter not working
                  Wes Reneau

                  Here you go! I've attached a workbook with it functioning. The only change was modifying your calculated field and then adding it to the Filters shelf with a value of 'T'.

                   

                  The change 1)checks that the parameter value matches a hard coded value, 2) that the data within the selected field is not null (contains a value).

                   

                  if [Levels Parameter] = 'All Invited' and not ISNULL([All Invited]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'L1' and not ISNULL([L1]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'BR1' and not ISNULL([BR1]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'BR2' and not ISNULL([BR2]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'SIL' and not ISNULL([SIL]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'GLD' and not ISNULL([GLD]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'L6' and not ISNULL([L6]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'L7' and not ISNULL([L7]) THEN 'T'

                  ELSEIF [Levels Parameter] = 'L8' and not ISNULL([L8]) THEN 'T'

                  ELSE 'F'

                  end

                   

                  I added the number of records so I could verify the results. This is based on my interpretation on what i assumed you were trying to do, so hopefully it's the desired outcome. If not, send an example of the desired outcome and i'll take another look.

                   

                  -Wes

                  • 6. Re: Parameter not working
                    Joshua Preston

                    Thanks Wes for being a Friday Friend and sending the solution in a sample .twbx AND in the body of the message. (Sidenote: I only have Tableau Public so I couldn't open your .twbx). I inserted your calc field into my file and it worked like a charm. Mission accomplished.

                     

                    I'm new to calculated fields, so this is really helpful to see a calc field formula in an example.

                     

                    Another question: I actually took a CASE formula from another workbook of mine (see attached) that had the same sparse data structure. To help me better understand calc fields, I put in your ELSEIF solution to replace the CASE formula that worked in that case, but the ELSE IF formula didn't work there.

                     

                    Any chance you can parse the difference for me?

                     

                    thanks.

                    • 7. Re: Parameter not working
                      Wes Reneau

                      No problem! Glad I was able to help.

                       

                      First off, nice dashboard! I couldn't find the calculated field that you tried ELSEIF in, but CASE statements are meant to test a single logical piece of information, while IF/ELSEIF allows testing of multiple conditions (and/or). I'm not sure if you were trying to use ELSEIF within a CASE statement, but that wouldn't work. All CASE statements can be written as IF statements. Personally, I prefer IF statements in Tableau, but use case quite a bit in other programs.

                       

                      Example:

                      CASE Statement: when name = 'Joshua' then 'T'

                      IF Statement: if name= 'Joshua' and state = 'Georgia' then 'T' (IF statements allows you to test multiple conditions Name AND State)

                       

                      Tableau's explanation of logical functions can be found here.

                      http://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_logical.html

                       

                      Does that help?

                      • 8. Re: Parameter not working
                        Joshua Preston

                        Hey Wes,

                         

                        Thanks a bunch. I lean more toward the data storytelling side of vizzes than the data calculating:) In typing this email I did my homework a little better and actually typed your ELSEIF statement CORRECTLY this time into the ASF2016 .twbx. And it worked! Whew:|

                         

                        Sorry I didn't point you to the CASE calc field. This is what worked for the ASF2016 .twbx when using a parameter for the categories of events:

                         

                        CASE [Topic Subject]

                        WHEN 'Arts/STEAM' THEN [Arts/STEAM]

                        WHEN 'Business & Careers' THEN [Business & Careers]

                        WHEN 'Chemistry' THEN [Chemistry]

                        WHEN 'Earth & Space Science' THEN [Earth & Space Science]

                        WHEN 'Engineering' THEN [Engineering]

                        WHEN 'Food & Drink' THEN [Food & Drink]

                        WHEN 'Health' THEN [Health]

                        WHEN 'Irreverent Fun' THEN [Irreverent Fun]

                        WHEN 'Life Sciences' THEN [Life Sciences]

                        WHEN 'Nature & Environment' THEN [Nature & Environment]

                        WHEN 'Science & Society' THEN [Science & Society]

                        WHEN 'Technology' THEN [Technology]

                        WHEN 'Physics' THEN [Physics]

                        WHEN 'Math' THEN [Math]

                        WHEN 'Sports & Fitness' THEN [Sports & Fitness]

                        ELSE ' '

                        END

                         

                        >>>>>>>>>>>>

                         

                        I'll start brushing up on my formula logic! Have a good weekend! And THANKS AGAIN.

                         

                        Josh

                        • 9. Re: Parameter not working
                          Joshua Preston

                          Hey Wes - I have a chance to do another test with logical functions, but I'm not sure if my data is structured the right way. (I have several hundred rows, so restructuring would be difficult.)

                           

                          I'm trying to get all the below technology topics (columns 3-5) in one dropdown filter (using a parameter) There is overlap in the 3 columns (i.e. Human-Computer Interaction is in two columns: Research Focus1 and Research Focus2)

                           

                           

                          I've been trying to figure out a logical function that can accomplish this. But any case statements or if statements only return unexpected values for the topic of choice. Would you be able to point me in the right direction? thanks.

                           

                           

                          Tableau workbook attached.

                          • 10. Re: Parameter not working
                            Wes Reneau

                            Hey Joshua,


                            Let me make sure I understand the problem. If the user selects Augmented Reality as Parameter 1, the desired result would be to display any records (rows) that contain Augmented Reality within Research Focus 1 OR

                            Research Focus 2 OR Research Focus 3? If that is correct, then update calculated field Research Focus CF - AR to the following.

                             

                            if [Research Focus1] = [Parameter 1] or [Research Focus2] = [Parameter 1] or [Research Focus3] = [Parameter 1]

                            then 'T'

                            else 'F'

                            end

                            The reason your version of Research Focus CF - AR didn't function when Parameter 1 was changed is the view didn't have any reference to Parameter 1. Parameters require a supporting calculated field within the Filters shelf to function.

                             

                            Here is an updated version of your original code that would make it function. Notice [Parameter 1] is referenced, rather than hardcoding 'Augmented Reality'. The OR statements above is a more eloquent way of writing this.

                            IF [Research Focus1] = [Parameter 1] and NOT ISNULL([Research Focus1]) THEN 'T'

                            ELSEIF [Research Focus2] =  [Parameter 1]and NOT ISNULL([Research Focus2]) THEN 'T'

                            ELSEIF [Research Focus3] =  [Parameter 1] and NOT ISNULL([Research Focus3]) THEN 'T'

                            ELSE ' '

                            END

                             

                            Does that help?

                            • 11. Re: Parameter not working
                              Joshua Preston

                              Wes, this did it! Thanks so much for looking it over and explaining how this works.

                               

                              So the only sticking point is how to get all fields to show up? (I put an "All" value in the parameter field (which has worked previously on my CASE statements, but it doesn't do it here).

                              • 12. Re: Parameter not working
                                Wes Reneau

                                Great, no problem!

                                 

                                'All' can be accomplished by hardcoding. It could be included within the first line as another condition, but I find it easier to read as an elseif statement. Essentially, it doesn't filter anything since we aren't requiring research focus 1,2,3 to match.

                                 

                                if [Research Focus1] = [Parameter 1] or [Research Focus2] = [Parameter 1] or [Research Focus3] = [Parameter 1]

                                then 'T'

                                ELSEIF [Parameter 1] = 'All'

                                then 'T'

                                else 'F'

                                end

                                • 13. Re: Parameter not working
                                  Joshua Preston

                                  This is all crystal clear! Thank you. I love how efficient your code is:)

                                  This thread will help me start focusing on logic functions (no more restructuring hundreds of rows in excel!)

                                   

                                  I'm just curious, what would this look like in a calc field?

                                  Wes wrote: "It could be included within the first line as another condition"

                                  • 14. Re: Parameter not working
                                    Wes Reneau

                                    Good Deal.

                                     

                                    It would look like:

                                    if  ([Research Focus1] = [Parameter 1] or [Research Focus2] = [Parameter 1] or [Research Focus3] = [Parameter 1])

                                    or  [Parameter 1] = 'All'

                                    then 'T'

                                    else 'F'

                                    end

                                     

                                    I placed parenthesis around ([Research Focus1] = [Parameter 1] or [Research Focus2] = [Parameter 1] or [Research Focus3] = [Parameter 1]) to show this is one evaluation and the second evaluation is

                                    [Parameter 1] = 'All'.  In this case the parenthesis just add clarity, however in some conditions the parenthesis would be required.

                                     

                                    I've attached a workbook that has a Good sheet and Bad sheet to help explain the importance of parenthesis in some conditions. Take a look at the calculated fields on both to see the differences. My example is based on the requirement to see all records where Research Focus 1 or 2 = Civic Computing (parameter 1) AND Research Focus 3 = Information Visualization (parameter 2). Parameter 2 is simply a duplicate of parameter 1. Hopefully this doesn't cause confusion, but it's a concept you'll eventually run into.

                                    1 2 Previous Next