6 Replies Latest reply on May 22, 2018 7:20 AM by Okechukwu Ossai

# Exclude / LOD Issues with ZN table calculations in joined datasets

I have one excel that contains the demand data by part number: e.g: the demand for Part a is 10. This demand would Not change over weeks.

I have another excel containing the parts supply by part number by Week: e.g: In Week 1, the supply for Part a is 2, and 9 for week 2.

then I left joined these two excels with "Part Number":

Since the parts Demand would not change, and the parts supply changes every week,

I want to calculate the total gap per week, whereas gap = max(sum([Demand Quantity]) -SUM( [Supply Quantity]),0)

I was using this calculated field called [Adjusted Parts Supply] to show the weekly supply:  ZN(lookup(sum({fixed [PartNumber-Demand],[Week]: SUM( [Supply Quantity])}),0)), which is fine. And  as per Deepak Rai 's great answer, I am able to pull out the Parts supply by removing nulls with 0: so the blue number 0 in the screenshot below shows correctly.

Then I created another calculated field called [Adjusted Parts Demand] in order to show the demand per part, which should stay the same for every single week:

ZN(lookup(sum({fixed [PartNumber-Demand]: avg([Demand Quantity]) }),0))

However, it shows differently as the yellow number 0 should be 2 as well (as the demand should not be changed every week). My best guess is that it has something to do with the fact that no parts supply on week 2 for part b , I keep searching online and even tried different LODs with "exclude" function to ignore Week to get the same demand number for each week.

If I could figure it out, then the final result is that I want to show the gap per week, on the aggregate: I used the following function to calculate the gap:

ZN(lookup(sum({fixed [PartNumber-Demand],[Week]: max(sum([Demand Quantity]) -SUM( [Supply Quantity]),0)  }),0))

Apparently the result for week 2 is wrong as tableau ignores part b for week 2:

Any advice would be greatly appreciated !!!!

I also attached my file (also thanks to Deepak Rai 's reminder! )

• ###### 1. Re: Exclude / LOD Issues with ZN table calculations in joined datasets

Deepak Rai

Sorry Deepak! Sorry about the abrupt reply, any chance you could also help me on this one please?

Really appreciate your great help on the other one!!

• ###### 2. Re: Exclude / LOD Issues with ZN table calculations in joined datasets

I don't know how you are calculating the GAP. I am able to fill 2 for 0. You know how to get Gap. Please  modify your calc to get it. I could not understand your logic.

Thanks

Deepak

If it Helps, Pl mark it Helpful and Correct to Close Thread

• ###### 3. Re: Exclude / LOD Issues with ZN table calculations in joined datasets

Hi Deepak, really appreciate the help!  However, (sorry I did not explain well in the original post), that "2" for part b in week 2 is actually more than a number 2, if the part demand for part b 10, then that number should be 10. Therefore, unfortunately I could not use your function as it "manually" assigns "2" to week 2 for part b.

Is there anyway I could assign " the demand for part b for week 1 " to week 2, or in other words, is there a way to assign same demand for each part to ALL weeks ?

• ###### 4. Re: Exclude / LOD Issues with ZN table calculations in joined datasets

Hi Nick,

Is this what you are looking for?

It will be very challenging finding a solution using your current approach. One thing about table calculations is that they are not very flexible in the way results are used outside the view they were calculated.

Another approach is to use cross join. The only concern is that cross joins can duplicate data. However, it is an approach which allows you to include  part B in week 2 of the supply table. LODs will then be used for most calculations in order to handle duplicates.

Step 2: Create calculated field [PartNumber-Supplied]

IFNULL({FIXED [Week], [PartNumber-Demand]: MIN(IF [PartNumber-Demand] = [PartNumber - Supply] THEN [PartNumber - Supply] END)}, [PartNumber-Demand])

Step 3: Create calculated field [Quantity Supplied]

IFNULL({FIXED [Week], [PartNumber-Demand]: MIN(IF [PartNumber-Demand] = [PartNumber - Supply] THEN [Supply Quantity] END)}, 0)

Step 4: Create calculated field [Quantity Demanded]

{FIXED [Week], [PartNumber-Demand]: MIN([Demand Quantity])}

Step 5: Create calculated field [Non Negative Diff.]

MAX([Quantity Demanded] - [Quantity Supplied], 0)

Step 6: Create calculated field [GAP Quantity]

{FIXED [Week]: SUM([Non Negative Diff.])}

Hope this helps.

Ossai

• ###### 5. Re: Exclude / LOD Issues with ZN table calculations in joined datasets

Hi Ossai,

Thank you sooooo much!!!!!! Everything you did perfectly matched exactly what I want !!!!

I am amazed starting from the first step!! (cuz I did not know how to do the join calculation with "1" blabla.. )

really appreciate your thorough answer and I could imagine it is time-consuming to write down the step-by-step calculations! !!

• ###### 6. Re: Exclude / LOD Issues with ZN table calculations in joined datasets

You're welcome Nick. Glad it helped.