topic Re: Pivot Table Aggr and Distinct Help in QlikView

Pivot Table Aggr and Distinct Help

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 06 Oct 2017 16:44:23 GMT

Count({<[compare AC PI Date]={1}>} distinct Delivery_OTIF)

Re: Pivot Table Aggr and Distinct Help

joshrussin — Fri, 06 Oct 2017 16:57:56 GMT

I wonder why the IF is coming up a little differently.

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 06 Oct 2017 17:03:02 GMT

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)

Re: Pivot Table Aggr and Distinct Help

joshrussin — Fri, 06 Oct 2017 17:09:25 GMT

Same as what I'm getting. I tried the same formula that excel uses for IF, but that throws the number completely off. The way I have it, gets it pretty close. Maybe I just need to figure out a new formula. I appreciate you toughing this one out and helping me with this. I've got the IF within 1100 orders total, but still not there yet.

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 06 Oct 2017 17:11:40 GMT

may be rounding issue for some rows. We were typing at the same time

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 06 Oct 2017 17:16:01 GMT

For most it is matching. let's focus on where it is not matching and take raw data for those ones to try and see what may be causing it

Re: Pivot Table Aggr and Distinct Help

joshrussin — Fri, 06 Oct 2017 17:30:28 GMT

I just went through the excel file, there are 220 IF for "OR" in the excel for sure.

Keep counting the IF in the Raw Pivot and keep coming up around 186 for them. So something is off in the original IF formula.

Re: Pivot Table Aggr and Distinct Help

joshrussin — Fri, 06 Oct 2017 19:37:32 GMT

I have tried a few different formulas and can't seem to figure out what is causing the issue. Either I am coming up with a completely wrong number or just blanks. Have you seen anywhere this could be catching up in the raw data?

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 06 Oct 2017 21:00:42 GMT

Not yet, I was away for a while. I will keep looking

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Sun, 08 Oct 2017 04:42:43 GMT

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;

Re: Pivot Table Aggr and Distinct Help

joshrussin — Mon, 09 Oct 2017 13:53:40 GMT

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.

Re: Pivot Table Aggr and Distinct Help

joshrussin — Mon, 09 Oct 2017 14:43:58 GMT

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

Re: Pivot Table Aggr and Distinct Help

joshrussin — Fri, 13 Oct 2017 15:38:12 GMT

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?

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 13 Oct 2017 16:35:50 GMT

Hi Joshua,

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

BR,

Vijay

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 13 Oct 2017 18:52:15 GMT

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";

Re: Pivot Table Aggr and Distinct Help

joshrussin — Fri, 13 Oct 2017 21:55:11 GMT

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.

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Fri, 13 Oct 2017 22:07:05 GMT

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

Re: Pivot Table Aggr and Distinct Help

joshrussin — Mon, 16 Oct 2017 13:30:22 GMT

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.

Re: Pivot Table Aggr and Distinct Help

vvira1316 — Mon, 16 Oct 2017 15:34:28 GMT

LOL

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

BR,

Vijay