Pivot Table Aggr and Distinct Help

[joshrussin]

Hey guys,

I have a pivot table that I am trying to find a way to get the Sum of "On-Time", "In-Full" as well as "On-time and In-full."

It has taken a lot to get to this point and a lot of help from the community. But I have a raw data file that I am trying to figure out what the percentages of deliveries that are On-Time and In-Full. In the attached excel file, shows the correct output I am trying to reach. In my QV file there are some expressions that I need help with. The raw data shows every item per delivery, not just deliveries. So for the Comp Pivot table, for the count of delivery, I had to do a DISTINCT for the delivery because I was getting 392 rather than the correct 19. For the other calculations I need, is there any way to get the sum based of the distinct deliveries rather than the raw item data. Here is what the output should look like.

And so far, the QV file is almost there, just need to figure out how to get the DISTINCT data per delivery for the following totals.

After those are based of the DISTINCT delivery #, I should be able to figure out the percentages below.

[vvira1316 · Accepted solution]

Hi Joshua,

It is caused by multiple rows for grouping by SaTy, Delivery_OTIF, Ac , and PI Date. as can be seen from attached excel file of OR Analysis

Script will have to be modified to get right result. I've simplified to get right values. You adapt it as per your need.

NoConcatenate

DataFile1:

LOAD

SaTy_OTIF & Chr(59) & Delivery_OTIF & Chr(59) & "Pl GI date_OTIF" & Chr(59) & "Ac GI date_OTIF" as "Table Key",

    Material_OTIF,

    Batch_OTIF,

    "Delivery quantity_OTIF",

    "Order Quantity_OTIF",

    Delivery_OTIF,

    "Pl GI date_OTIF",

    "Ac GI date_OTIF",

    SU_OTIF,

    "Sales Doc_OTIF",

     SaTy_OTIF

FROM [lib://DataFolder/OTIF Data Old_August.XLSX]

(ooxml, embedded labels, table is [RAW DATA])

;

NoConcatenate

DataFile2:

Load

    Sum("Delivery quantity_OTIF") as "Sum Delivery quantity_OTIF",

    Sum("Order Quantity_OTIF") as "Sum Order Quantity_OTIF",

    If((Sum("Delivery quantity_OTIF") - Sum("Order Quantity_OTIF")) >= 0, 1, 0) as "Qty IF if Positive",

    If((num(date([Ac GI date_OTIF]))-num(Date([Pl GI date_OTIF]))) <= 0, 1, 0) as "compare AC PI Date",

If((Sum("Delivery quantity_OTIF") - Sum("Order Quantity_OTIF")) >= 0 and

    (num(date([Ac GI date_OTIF]))-num(Date([Pl GI date_OTIF]))) <= 0, 1, 0) as OTIF,

SaTy_OTIF & Chr(59) & Delivery_OTIF & Chr(59) & "Pl GI date_OTIF" & Chr(59) & "Ac GI date_OTIF" as "Table Key"

Resident DataFile1

Group By SaTy_OTIF, Delivery_OTIF, "Ac GI date_OTIF", "Pl GI date_OTIF";

Drop Table DataFile1;

[joshrussin]

Oh wow, I would have never seen that. Thank you so much. I will be working on this later today. How could I edit the list instead of the filter to only have the specific types I need? Also a way to group them as Internal, Domestic, or OUS? Kind of like the IF statement I had before.

[joshrussin]

Nevermind, I just wrapped that within an IF statement and worked.

[joshrussin]

Hey Vijay,

I tried to run the report for September now instead of August and now there are differences. Can you take a look please?

[vvira1316]

Hi Joshua,

Sure. I'll take a look at it and let you know.

BR,

Vijay

[vvira1316]

Hi Joshua,

Any reason there is change in time delta calculation in Excel? Please see the screen shot below.

Is it suppose to be Ac - Pl or Pl - Ac

In Excel it is Pl - Ac (OT calculation formula is "=IF(G510<=0,1,0)") This is causing difference and it is change in logic. Please confirm.

In script it is Ac - Pl

    "Pl GI date_OTIF",
    "Ac GI date_OTIF",
    SU_OTIF,
    "Sales Doc_OTIF",
     SaTy_OTIF
FROM [lib://qlikid_joshrussin/sept_otif_my_version.XLSX]

//Previous Month
// FROM [lib://qlikid_joshrussin/OTIF Data.XLSX]
//From August (only one that completely matches up
// FROM [lib://qlikid_joshrussin/OTIF Data Old_August.XLSX]
//From September with data I pulled
// FROM [lib://qlikid_joshrussin/OTIF Data_Sept_Me.XLSX]
//From September with data James pulled
// FROM [lib://qlikid_joshrussin/OTIF Data_Sept_James.XLSX]
//From July (somewhat matches up)
// FROM [lib://qlikid_joshrussin/July OTIF.XLSX]
(ooxml, embedded labels, table is [RAW DATA]);
;

NoConcatenate
DataFile2:
Load
    Sum("Delivery quantity_OTIF") as "Sum Delivery quantity_OTIF",
    Sum("Order Quantity_OTIF") as "Sum Order Quantity_OTIF",
    If((Sum("Delivery quantity_OTIF") - Sum("Order Quantity_OTIF")) >= 0, 1, 0) as "Qty IF if Positive",
    If((num(date([Ac GI date_OTIF]))-num(Date([Pl GI date_OTIF]))) <= 0, 1, 0) as "compare AC PI Date",
If((Sum("Delivery quantity_OTIF") - Sum("Order Quantity_OTIF")) >= 0 and
    (num(date([Ac GI date_OTIF]))-num(Date([Pl GI date_OTIF]))) <= 0, 1, 0) as OTIF,
SaTy_OTIF & Chr(59) & Delivery_OTIF & Chr(59) & "Pl GI date_OTIF" & Chr(59) & "Ac GI date_OTIF" as "Table Key"
Resident DataFile1
Group By SaTy_OTIF, Delivery_OTIF, "Ac GI date_OTIF", "Pl GI date_OTIF";

[joshrussin]

Qlik Formula is right, one of the clients ran the data in excel and their data formula was wrong. They read the workflow backwards. So from here on out, we should be good with this table. Thank you so much for your patience and help. I really can't thank you enough.

[vvira1316]

I'm glad that it was their mistake, now they can understand that we put in lot of effort to get information right....

[joshrussin]

Oh they apologized a lot for it, the bad thing was they already submitted what they came up with to their superiors and had to tell them they were wrong.

[vvira1316]

LOL

Hi, Did you get answer on your other question you had posted?

BR,

Vijay