Pivot table help

[joshrussin]

Hey guys,

I am making a pivot table showing On Time In Full...

I have gotten to where I need to get the sum of In Full.

for my pivot table, this is my expression for In Full:

if((sum([Delivery quantity_OTIF]) - sum([Order Quantity_OTIF])) >= 0, 1, 0)

Now I am trying to get the sum of all of that data.

I am trying with this equation:

Sum(if((sum([Delivery quantity_OTIF]) - sum([Order Quantity_OTIF])) >= 0, 1, 0))

but I keep getting an error saying "Nested aggregation not allowed"

Any tips or help?

I'm just a little confused I guess because this equation DID work:

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

[vvira1316]

Hi,

The issue is that sum operation is being done on another sum. That is where it is nested...

sum(if((

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

    and

    (Sum(If([Qty IF]>=0,1,0))) ),

    1,0))

Can you please advise what you are trying to do? let me understand that and we can try to find a solution to it

[joshrussin]

Essentially just trying to count Row Labels when items are OT and IF. Output should be the same as attached above.

[vvira1316]

Hi Joshua,

I'm still not sure what exactly the calculation should be.

This is what I'm thinking based on expression you have

Let me know if this will help and if I can help you further

NoConcatenate
DataFile1:
LOAD
    Material_OTIF,
    Batch_OTIF,
    "Delivery quantity_OTIF",
    "Order Quantity_OTIF",
    "Delivery quantity_OTIF" - "Order Quantity_OTIF" as "Qty IF",
    If(("Delivery quantity_OTIF" - "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",
    Delivery_OTIF,
    "Pl GI date_OTIF",
    "Ac GI date_OTIF",
    SU_OTIF,
    "Sales Doc_OTIF",
    SaTy_OTIF
FROM [lib://DataFile/OTIF Data.xlsx]
(ooxml, embedded labels, table is [RAW DATA]);

NoConcatenate
DataFile2:
Load
Sum("Qty IF if Positive") as "Sum Qty IF if Positive",
    Sum("compare AC PI Date") as "Sum compare AC PI Date",
    SaTy_OTIF
Resident DataFile1
Group By SaTy_OTIF;

[joshrussin]

This works,

sum(if([Qty IF if Positive] = [compare AC PI Date],1,0))

Would you happen to know why the data shows differently?

Meaning, the excel pivot tables vs the Qlik pivot tables. They are the same data, I thought it was because Excel only looks at the 1st 1000 rows, but apparently that's not it.

[vvira1316]

Hi Joshua,

I'm away from my system till tomorrow. When you say it shows differently, I'm not getting what the difference is and could be. It could be due to difference in formula for excel and QV calculations, data arrangement, but I'm just guessing until I can see both of them together.

So are QV values correct or not?

BR,

Vijay

[joshrussin]

My client says that their numbers are correct via the excel file. I will be going over them again throughout the week. I believe the QV values are correct though.

[joshrussin]

Actually I'm kind of confused now. How can there be more OnTimeInFull items than items that are just OnTime?

Attached is the updated QV file.

[vvira1316]

Hi Joshua,

I'm checking. Will update you.

BR,

Vijay

[vvira1316]

Hi Joshua,

I think it has to do with formula you were using for following

sum(if((

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

    and

    (Sum(If([Qty IF]>=0,1,0))) ),

    1,0))

sum(if([Qty IF if Positive] = [compare AC PI Date],1,0))

Not sure if this matches with what you originally intended to calculate.

I was not able to understand your original calculation but you thought above worked for you. Please check that

BR,

Vijay

[joshrussin]

That calculation seems right, if you look at the excel file and look at the pivot table on the left hand side of "Comp Pivot", that data doesn't match up to the "Comp Pivot" in Qlik. The raw pivot, from what I can tell, matches.