0 Replies Latest reply: Jul 9, 2014 2:01 PM by Joe Hutchings RSS

    Summarizing Data for Charting Percentage

    Joe Hutchings

      Hello --

       

      I have a need to generate a supplier performance dashboard in Qlik. We will measure our suppliers on 3 metrics

      1) On-Time Delivery %

      2) Full Delivery %

      3) Complete Delivery & On-Time %

       

      I am struggling with #2 and #3 in this list, due to the way the data is split up in the data source. I started going down the road of pre-aggregating my data in SQL queries before it gets to Qlik, but I believe there is a better way.

       

      For #2 - I need to calculate full delivery percentage for supplier deliveries. The trouble is that sometime we receive 1 delivery split up in 2 or 3 lines of data. The raw data looks like this:

       

      PO #PO LinePO Line Release #Promise DateReceived DatePO Receipt IDRelease QtyReceived Qty
      123116/10/20146/8/201412345025
      123116/10/20146/8/201412355025
      123127/1/20147/1/2014123615050
      123127/1/20147/1/20141237150100

       

      For this PO Release, all the delivered listed should be considered 100% fully delivered because even though we split the receipts 100% of the PO release qty was received on the same day.

       

      The chart should look as follows in this case:

      Capture.PNG.png

       

       

      For #3, I will have the same set of data - but this time I need to also check if the total qty was received in full no earlier than 7 days BEFORE the promise date, and 1 day AFTER the promise date. I will then calculate the % of total of these that match the criteria.

       

      I am struggling with how to do this without adding a lot more dimensions than I want to my chart. Can someone help me please?