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))

[YoussefBelloum]

Hi,

the second equation work because date() is not an aggregation function, it is a scalar function

Read this: Aggregations and Function Classes

To make it works, you should add the Aggr() function between the two Sums.

like this:

Sum(...)

Sum(Aggr(Sum(...),...))

Sum(Aggr(Sum(Aggr(Sum(...),...)),...))

Sum(Aggr(Sum(Aggr(Sum(Aggr(Sum(...),...)),...)),...))

Hope it helps

YB

[joshrussin]

I tried this, but it doesn't actually show the sum.

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

[shree909]

Try to add the dimensions to the Aggr list .

Please provide a sample qvw file with the expected output.

[joshrussin]

Thanks for trying to help, here is the files.

If you are able to do this, the results will vary because excel only pulls the 1st 1000 rows from the data.

[vvira1316]

Are you expecting following?

Formula

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

[joshrussin]

Somewhere around these results. At least this much. The results from Qlik may be a little higher.

[vvira1316 · Accepted solution]

LOAD

    Material_OTIF,

    Batch_OTIF,

    "Delivery quantity_OTIF",

    "Order Quantity_OTIF",

    "Delivery quantity_OTIF" - "Order Quantity_OTIF" as "Qty IF",

    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]);

Expression

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

[joshrussin]

how did I not think of this....

Thank you so much!

[joshrussin]

I am trying to do the Sum of OTIF now and getting the same message. How would you go about this?

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))