Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Accepted Solutions
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;
Hi Joshua,
I'm not sure why IF is having issuebut OT is working in both cases as can be seen from screen shots.
Formulas
Count({<[compare AC PI Date]={1}>} distinct Delivery_OTIF)
/
Count(distinct Delivery_OTIF)
