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

# Creating calculated fields in multi-columned table with several subrows

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

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

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