15 Replies Latest reply: Aug 27, 2017 10:51 AM by Steve Br. RSS

    Problem with set analysis

    Steve Br.

      Hi all,

       

      For days now I am struggling to get my expression working and need your help. I'd like to calculate the percentage of orders that have higher than the requested quantity. This should be done only for orders with the same unit of measure and displayed in pivot table per department. Below a simplified script:

       

      Data:
      Load*Inline
      [
      Order|Item|Requested|Requested_Unit|Ordered|Ordered_Unit|Department
      O100|I10|2|T|3|T|Materials
      O100|I20|2000|KG|3|T|Materials
      O200|I10|30|KG|70|KG|Materials
      O200|I20|50|KG|50|KG|Packaging
      ]
      (Delimiter is '|');
      
      Table:
      Load
          If(Requested_Unit=Ordered_Unit, 'Same_unit', 'Different_unit') as Unit, 
          Order,
          Item,
          Requested,
          Requested_Unit,
          Ordered,
          Ordered_Unit,
          Department
      Resident Data;
      Drop Table Data;
      
      
      
      

       

      For order O100, only the first line should be taken into account, as the second line has different units (KG and T).

      For order O200 both lines should be considered since the unit of measure is the same (KG).

       

      Therefore, the result should be:

      Department Materials: 100% (2 orders with Ordered > Requested out of 2 orders in total)

      Department Packaging: 0% (0 orders with Ordered > Requested out of 1 orders)

       

      Please note that all data needs to be loaded and nothing can be excluded in the script. I tried with this expression in pivot table...

       

      Count({<Unit={'Same_unit'}, Order={"=Sum(Ordered) > Sum(Requested)"}>}Distinct Order)
      / 
      Count({<Unit={'Same_unit'}>}Distinct Order)
      

       

      ...but it gives totally wrong results. It does not consider O100 at all (probably because one of the entries has different measures) and counts O200 as Ordered > Requested for both departments (not sure why, maybe it takes the sum of quantity for the entire order and not per department).

       

      I really hope that somebody can help me out here. Cheers!