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

    Exclude / LOD Issues with ZN table calculations in joined datasets

    nick liu

      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:


      [Adjusted Parts Demand]:

      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! )