1 Reply Latest reply on Feb 19, 2013 8:25 PM by Noel Avison

    Trouble with CASE and aggregate fields

    Noel Avison

      I am having trouble with this CASE statement:

       

       

      CASE [YorX]  

          WHEN "X" THEN CASE [Color Key]

                              WHEN "Z:Empty Space" THEN [X-Full]

                              WHEN "X:Total Bins In Row" THEN CASE LEFT([Point Type],2)

                                                                  WHEN "X1" THEN CASE [Polygon Type]

                                                                                      WHEN "CR-3" THEN  [X-Full]

                                                                                      WHEN "CR-4" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "CR-5" THEN  [X-Full]

                                                                                      WHEN "CR-6" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "CR-7" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "CR-9" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "FUM-1" THEN [X-Full]

                                                                                      WHEN "SM-1" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))                                                                            

                                                                                      WHEN "SM-2" THEN  [X-Full]

                                                                                      WHEN "SM-3" THEN  [X-Full]

                                                                                      WHEN "SM-4" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                  END

                                                                  WHEN "X2" THEN CASE [Polygon Type]

                                                                                      WHEN "CR-3" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "CR-4" THEN  [X-Full]

                                                                                      WHEN "CR-5" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "CR-6" THEN  [X-Full]

                                                                                      WHEN "CR-7" THEN  [X-Full]

                                                                                      WHEN "CR-9" THEN  [X-Full]

                                                                                      WHEN "FUM-1" THEN([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "SM-1" THEN  [X-Full]                                                                            

                                                                                      WHEN "SM-2" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "SM-3" THEN ([X-Full]+[Polygon Fill Code]*(1-[% Of Fill]))

                                                                                      WHEN "SM-4" THEN  [X-Full]

                                                                                  END

                                                              END

                              WHEN "A:Calculated Parameter" THEN CASE LEFT([Point Type],2)

                                                                      WHEN "X1" THEN CASE [Polygon Type]

                                                                                         WHEN "CR-3" THEN  [X-Full]

                                                                                         WHEN "CR-4" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                         WHEN "CR-5" THEN  [X-Full]

                                                                                         WHEN "CR-6" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                         WHEN "CR-7" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                         WHEN "CR-9" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                         WHEN "FUM-1" THEN [X-Full]

                                                                                       WHEN "SM-1" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))                                                                            

                                                                                         WHEN "SM-2" THEN  [X-Full]

                                                                                         WHEN "SM-3" THEN  [X-Full]

                                                                                         WHEN "SM-4" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                      END

                                                                  WHEN "X2" THEN CASE [Polygon Type]

                                                                                      WHEN "CR-3" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                      WHEN "CR-4" THEN  [X-Full]

                                                                                      WHEN "CR-5" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                      WHEN "CR-6" THEN  [X-Full]

                                                                                      WHEN "CR-7" THEN  [X-Full]

                                                                                      WHEN "CR-9" THEN  [X-Full]

                                                                                      WHEN "FUM-1" THEN([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                      WHEN "SM-1" THEN  [X-Full]                                                                            

                                                                                      WHEN "SM-2" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                      WHEN "SM-3" THEN ([X-Full]+[Polygon Fill Code]*(1-[SelectorCodeCase]))

                                                                                      WHEN "SM-4" THEN  [X-Full]

                                                                                  END

                                                                      END

                          END

          WHEN "Y" THEN [X-Full]

      END

       

      This equation is fine, but I wanted to replace [SelectorCodeCase] with a new field from a different data source. The problem is, that datasource has multiple values for each location so I have to sum them to get a value I want to apply to this equation. When I try to plug this new field into the case statement it says I cannot blend aggregate and non aggregate together.  Basically I want these values on the last sheet to be attributed to the corresponding "Location" from the first data source so that when I use the case statement, it uses those values for each location.

       

           I am having trouble figuring out how to go about doing this. Also I am having a hard time explaining the problem. Basically, the fields called "X Case" and "Y Case" need to be manipulated using the values generated by the field "TEST FIELD" and I am having trouble getting the relationship to work the way I want it to. Each polygon has a field called "Row" that I want to attribute the values in the "Test Field" field that are generated when I put "Test Field" in the Text box and "Location" (Which is the same as "Row" on the other data source) into the Rows box.

       

           Currently, the values are already present in the data source. However I don't want to include every possible combination of values in the data source like I have been doing because that takes over a hundred columns, so to save the time of making that I am having tableau calculate the calculations for me. Here is what the data for the polygons looks like.


      BuildingYorXPolygon Fill CodePolygon TypeIdentifierColor KeyRowXYPointNbrPoint TypeX-FullY-Full% Of FillACS GroupDate UpdatedMax BinsNbr of Bins In LocM1/PP %Inshell %

       

      46001ACSY43.6ACS-146001X:Total Bins In Row46001302962711X1,Y13029627190.00%1 and 22/13/201340390.90
      46001ACSY43.6ACS-146001X:Total Bins In Row46001303662712X2,Y13036627190.00%1 and 22/13/201340390.90
      46001ACSY43.6ACS-146001X:Total Bins In Row4600130366227.43X2,Y230366227.490.00%1 and 22/13/201340390.90
      46001ACSY43.6ACS-146001X:Total Bins In Row4600130296227.44X1,Y230296227.490.00%1 and 22/13/201340390.90

       

      I want the sum of "Test Field" grouped by Location or Row to add those values into this data source. This is what the data from "Test Field" looks like

       

       

       

       

      Polygon TypeLocationMax BinsLPN00-All01-M1/PP02-Inshell03-Inputs04-Byproducts05-Rst/Mfg10-Finished Goods01-Raw >30 Days02-Raw Mfg Inputs > 30 Days03-Pasteurized Inventory > 14 Days04-WIP Mfg/Rst > 14 Days05-Byproducts (Raw) >7 Days06-FGI Mfg/Rst > 60 Days99-Don’t ShowAvailable/OkHold/Rework

       

      ACS-14600140403277861100000000000110
      ACS-14600140404917381001000000000110
      ACS-14600140402986111100000000000110
      ACS-14600140850184301010000000000110
      ACS-14600140404450591000001000000110
      ACS-14600140400086511100000000000110
      ACS-14600140403055441100000000000110
      ACS-14600140404375081000001000000110
      ACS-14600140404731931000100000010010
      ACS-14600140404856741100000000000110
      ACS-14600140403816081000001000000110
      ACS-14600140402844861100000000000110
      ACS-14600140401974681100000000000110
      ACS-14600140401684131100000000000110
      ACS-14600140404301811000001000000110
      ACS-14600140404426771100000000000110
      ACS-14600140404892401100000000000110
      ACS-14600140400993801100000000000110
      ACS-14600140401201991100000000000110
      ACS-14600140404493361100000000000110
      ACS-14600140405074041001000000000110
      ACS-14600140404366691100000000000110
      ACS-14600140400207301100000000000110
      ACS-14600140404770881000010000000110
      ACS-14600140404769681000010000000110
      ACS-14600140404316531000001000000110
      ACS-14600140404654061100000000000110
      ACS-14600140400586511100000000000110
      ACS-14600140850117331000100000010010
      ACS-14600140401185891100000000000110
      ACS-14600140405023241000100000000110
      ACS-14600140850237021010000000000110
      ACS-14600140850174521010000000000110
      ACS-14600140404372861000001000000110
      ACS-14600140404640371001000000000110
      ACS-14600140260329451100000000000110
      ACS-14600140404998151000100000000110
      ACS-14600140401907731100000000000110
      ACS-14600140400284601100000000000110

       

      The field I created, "Test Field" multiplies various columns together depending on the parameters selected by the user. If it returns a 1, then if it meets all the criteria then it is true. I want the field to add up all the true values for each "Location" so that I can use this to alter the size of my polygons.

        • 1. Re: Trouble with CASE and aggregate fields
          Noel Avison

          Ok, I think maybe I need to clarify my objective a bit here.

           

          If I was to do this in excel, I would do a sumif function in the first table. And add all the 1's in a specific column which is the calculated field "Test Field" on the second table that have a location that matches the location(row) in the first table. I want a new field on the first data source that would give the sum of all the values grouped by location. Now the field that generates those numbers on the second table is created using user input in tableau. So I don't know how to achieve the same result as a sumif function of all the 1's that have a the same locations as the polygons from my first data source.

           

          So basically I want it to return a sumif function of the field called "Test Field" using location as a criteria so that each location would have a new number based on the selections the user inputs in order to have a new field on my first table that had values grouped by location. This field is what needs to go into my case statement.