7 Replies Latest reply on Oct 18, 2013 8:53 AM by Matt Lutton

    Calculated Field - Return multiple values

      I am attempting to group a set of items into three categories.

      1. All values that match a particular condition
      2. All values that do not match that same condition
      3. Every value, regardless of matching


      I have created a calculated field based on the first two categories and applied that to color in a line graph. This splits the results into two lines just as I had hoped it would. Now my problem lies with creating that third line. Is it possible to return multiple results from a calculated field? If not is there another way of approaching this problem?


      I have thought about trying to do a combined graph, but the colors would be applied to both results and I would just end up with the same lines on top of each other instead of the third line I am trying to get.




      Added image of what I have now, showing the two lines.

        • 1. Re: Calculated Field - Return multiple values
          Mark Holtz

          To paraphrase you, you are looking to get a line representing the un-split total?

          So line1+line2 = line3


          Could you add whatever Measure (green pill) you used in the Control-Test Calculated field that you created onto the Rows Shelf and then use dual axes and sychronize them?

          • 2. Re: Calculated Field - Return multiple values

            Yes, but I want to have 3 lines, not just combine the two that I have into a third line. The "TPValueTotal" contains the data that is being displaying in the graph. All that the ControlTest dimention does is split that into two lines.


            What would be really nice is if in the calculated field I could return one record as two different records (just return twice)


            Non-functional example:


            IF [A_Dim] == [Var_1] AND [B_Dim] == [Var_2] THEN

                'Test' AND 'Baseline'

            ELSEIF [A_Dim] == [Var_2] AND [B_Dim] == [Var_1] THEN

                'Test' AND 'Baseline'


                'Control' AND 'Baseline'



            But I don't think the AND syntax there is allowed.

            • 3. Re: Calculated Field - Return multiple values
              Mark Holtz

              Hmmm... another thought would be to create 3 measures instead of using a dimension to try to split 1 measure as you're describing.


              Measure 1:"Control"

              IF "Control Criteria" THEN [TPValueTotal] ELSE 0 END


              Measure 2: "Test"

              IF "Test Criteria" THEN [TPValueTotal] ELSE 0 END


              Measure 3: "TPValueTotal"


              Then add the Measure Values to the Columns Shelf and color by the Measure Names dimension (and filter the Measure Names to show only those 3 Measures).

              2 of 2 people found this helpful
              • 4. Re: Calculated Field - Return multiple values

                I will give that a try, I think that will work. The only problem is that I am calculating percentiles in memory, so having three separate percentile calculations will be significantly slower. I will let you know how it goes though.



                Edit: When I attempt that (creating the first measure) I received an error that  "cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions. Any ideas?

                • 5. Re: Calculated Field - Return multiple values
                  Noah Salvaterra

                  Is it possible to create these measures prior to aggregating TPValueTotal? i.e. create filtered measures on the underlying data for TPValueTotal, say TPRaw. Then you'd have TPRaw, TPRawTest and TPRawControl and could aggregate each of these in the same way (TPValueTotal, TPValueControlTotal and TPValueTestTotal).


                  Mixing aggregated with non-aggregated measures can create a many to one relationship, Tableau plays it safe in these situations by sending you an error. If there is no ambiguity, or you're a risk taker, you can wrap token aggregation around the items being compared, i.e min(A_Dim)==min(Var_1).


                  Looking at Mark's first post, it seems like that might also give the result you what you're looking for. If you create a synchronized dual axis chart with 2 copies of TPValueTotal then select multiple mark types and remove Control-Test-baseline from the marks card for one of the copies of TPValueTotal. This will give 3 lines.

                  • 6. Re: Calculated Field - Return multiple values

                    Hi Tony,


                    I don't know if you are still reachable by this 2012 posting but I wanted to ask you for the formula structure you used to get two separate values out of a single calculated field.





                    • 7. Re: Calculated Field - Return multiple values
                      Matt Lutton



                      I'd post a new thread in the forum to ask your specific question, with details about your scenario and a packaged workbook.  With those details, someone on the forum will help you out.