
1. Re: Exclude / LOD Issues with ZN table calculations in joined datasets
nick liu May 16, 2018 3:24 PM (in response to nick liu)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
Deepak Rai May 16, 2018 9:29 PM (in response to nick liu)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

Testing (2)_v10.2.twbx 33.2 KB


3. Re: Exclude / LOD Issues with ZN table calculations in joined datasets
nick liu May 17, 2018 5:27 AM (in response to Deepak Rai)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 ?
Much appreciate your help and your valuable time !!!!!!!!!!

4. Re: Exclude / LOD Issues with ZN table calculations in joined datasets
Okechukwu Ossai May 18, 2018 6:45 PM (in response to nick liu)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 1: Create a cross join using join calculation 1=1
Step 2: Create calculated field [PartNumberSupplied]
IFNULL({FIXED [Week], [PartNumberDemand]: MIN(IF [PartNumberDemand] = [PartNumber  Supply] THEN [PartNumber  Supply] END)}, [PartNumberDemand])
Step 3: Create calculated field [Quantity Supplied]
IFNULL({FIXED [Week], [PartNumberDemand]: MIN(IF [PartNumberDemand] = [PartNumber  Supply] THEN [Supply Quantity] END)}, 0)
Step 4: Create calculated field [Quantity Demanded]
{FIXED [Week], [PartNumberDemand]: 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

DemandSupply Gap_OO.twbx 31.5 KB


5. Re: Exclude / LOD Issues with ZN table calculations in joined datasets
nick liu May 22, 2018 7:06 AM (in response to Okechukwu Ossai)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 timeconsuming to write down the stepbystep calculations! !!

6. Re: Exclude / LOD Issues with ZN table calculations in joined datasets
Okechukwu Ossai May 22, 2018 7:20 AM (in response to nick liu)You're welcome Nick. Glad it helped.