3 Replies Latest reply on Oct 3, 2018 10:38 AM by Jim Dehner

    Creating calculated fields in multi-columned table with several subrows

    Joe Applegate

      Hi,

       

      I have a data set that has various ISBN numbers (think of them as product IDs) in various specific locations (signified by location code) and Region. The Calculated Volume column is the cubic volume of the location in that region, and the aggregate volume is the cubic volume of the # of ISBNs in that location (number of items in location x unit volume).

       

       

      ISBN

      Location Code

      Region

      Calculated Volume

      Aggregate Volume

      Number of Items in location

      11142

      1010

      A

      57,000

      24,000

      1

      1011

      B

      99,000

      24,000

      1

      11143

      3400

      C

      40,000

      10,000

      1

      3500

      D

      60,000

      30,000

      3

      11144

      4100

      E

      30,000

      12,000

      4

      4101

      E

      30,000

      15,000

      5

      4103

      E

      30,000

      15,000

      5

       

      This is a bit complicated so bear with me... I would like to know if certain location codes are eligible to be consolidated to. Basically, can we shuffle around the same item that is spread out in various locations into 1 location to save space. More specifically, I'd like to know if the SUM of the Aggregate Volumes within an item/ISBN is LESS than the Calculated Volume of the Location Code with the higher Number of Items in it. If so, that location get's an "Eligible"

       

      If the Number of Items in a Location are EQUAL, then the SUM of the Aggregate Volumes should be compared to the greater Calculated Volume among the group of Location Codes. If the SUM of the Aggregate Volumes is less than that higher Calculated Volume, the Location code with the higher Calculated Volume is "Eligible".

       

      Desired Output:

       

      ISBN

      Location Code

      Eligibility

      Region

      Calculated Volume

      Aggregate Volume

      Number of Items in location

      11142

      1010

      Null

      A

      47,000

      24,000

      1

      1011

      Eligible

      B

      99,000

      24,000

      1

      11143

      3400

      Null

      C

      40,000

      10,000

      1

      3500

      Eligible

      D

      60,000

      30,000

      3

      11144

      4100

      Null

      E

      30,000

      12,000

      4

      4101

      Null

      E

      30,000

      15,000

      5

      4103

      Null

      E

      30,000

      15,000

      5

       

      In ISBN 11142, the SUM of the Aggregate Volumes is 48,000. Since the Number of Items in Location is EQUAL to one another, the 48,000 sum is compared to the greater Calculated Volume within that ISBN, which is 99,000. Since 48,000 < 99,000, Location Code 1011 is "Eligible".

       

      In ISBN 11143, the SUM of the Aggregate Volumes is 40,000. This is compared to 60,000 since Location Code 3500 has more items in it than Location 3400. Since 40,000 < 60,000 Location Code 3500 is "Eligible".

       

      In the final case of ISBN 11144, the SUM of the Aggregate Volumes is 42,000. This is compared to the Location Code with the higher Number of Items. In this case, that is either Location code 4101 or 4103. (I'm not sure if we can make logic that says compare to either one, since it can't pick one with the greater calculated volume since they're in the same Region and equal), and thus it should be indifferent) and since 42,000 > 30,000, all Location Codes for that ISBN are Null.

       

      I know that's a lot of logic and I'm not sure if it can be executed in Tableau, but any help would be appreciated! I've been spinning my wheels for quite some time... I recreated my data set in these mock tables since what I'm working on can't exactly be shared, so I also appreciate the flexibility. Thank you!

        • 1. Re: Creating calculated fields in multi-columned table with several subrows
          Jim Dehner

          Joe - really would prefer to look at your TWBX workbook to do this -

          That said I will give you a conceptual way to go and you will need to fight through the actual detail

           

          The total aggregate volume for a isbn is    {fixed [isbn]:sum([aggregate volume])}

           

          you need to compare that to the [ space available]  ==  {fixed [isbn],[location code]:[sum([calculated volume])}

           

          but at the isbn level   {fixed [isbn]: max([space available)]

           

          then do you comparison between the formula of max space at an isbn to the total volume needed

           

          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.

          1 of 1 people found this helpful
          • 2. Re: Creating calculated fields in multi-columned table with several subrows
            Joe Applegate

            This helped a lot, so thank you! The only part I am stuck on now is marking locations with an "N" or Null

             

            How do I make it so the output of "Eligible" is only on on the location that has the MAX Calculated volume? So, using what you had provided, I get an output looking like this for ISBN 1143:

            ISBN

            Location Code

            Eligibility

            Region

            Calculated Volume

            Aggregate Volume

            Number of Items in location

            11143

            3400

            Eligible

            C

            40,000

            10,000

            1

            3500

            Eligible

            D

            60,000

            30,000

            3

             

            How do I make it so that Eligibility for Location Code 3400 (which has the smaller Calculated Volume) show as "N", since the SUM of Aggregate is being compared to the MAX(Calculated Volumes)? Ideal output as seen below:

             

            ISBN

            Location Code

            Eligibility

            Region

            Calculated Volume

            Aggregate Volume

            Number of Items in location

            11143

            3400

            NO

            C

            40,000

            10,000

            1

            3500

            Eligible

            D

            60,000

            30,000

            3

            • 3. Re: Creating calculated fields in multi-columned table with several subrows
              Jim Dehner

              Joe - you marked your own second question correct - please mark my reply correct instead

              thanks

              Jim