topic Re: Pivot table help in QlikView

Pivot table help

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

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

Re: Pivot table help

YoussefBelloum — Thu, 28 Sep 2017 15:50:26 GMT

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

Re: Pivot table help

joshrussin — Thu, 28 Sep 2017 20:05:33 GMT

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

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

Re: Pivot table help

shree909 — Thu, 28 Sep 2017 20:39:10 GMT

Try to add the dimensions to the Aggr list .

Please provide a sample qvw file with the expected output.

Re: Pivot table help

joshrussin — Thu, 28 Sep 2017 20:56:32 GMT

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.

Re: Pivot table help

vvira1316 — Thu, 28 Sep 2017 21:37:26 GMT

Are you expecting following?

Formula

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

Re: Pivot table help

joshrussin — Thu, 28 Sep 2017 21:41:31 GMT

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

Re: Pivot table help

vvira1316 — Thu, 28 Sep 2017 22:33:03 GMT

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

Re: Pivot table help

joshrussin — Fri, 29 Sep 2017 03:17:42 GMT

how did I not think of this....

Thank you so much!

Re: Pivot table help

joshrussin — Fri, 29 Sep 2017 13:04:10 GMT

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

Re: Pivot table help

vvira1316 — Fri, 29 Sep 2017 15:19:12 GMT

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

Re: Pivot table help

joshrussin — Fri, 29 Sep 2017 16:09:14 GMT

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

Re: Pivot table help

vvira1316 — Fri, 29 Sep 2017 16:55:34 GMT

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;

Re: Pivot table help

joshrussin — Tue, 03 Oct 2017 20:09:27 GMT

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.

Re: Pivot table help

vvira1316 — Wed, 04 Oct 2017 11:36:42 GMT

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

Re: Pivot table help

joshrussin — Wed, 04 Oct 2017 14:59:32 GMT

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.

Re: Pivot table help

joshrussin — Wed, 04 Oct 2017 15:34:31 GMT

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.

Re: Pivot table help

vvira1316 — Thu, 05 Oct 2017 19:52:33 GMT

Hi Joshua,

I'm checking. Will update you.

BR,

Vijay

Re: Pivot table help

vvira1316 — Thu, 05 Oct 2017 20:08:09 GMT

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

Re: Pivot table help

joshrussin — Thu, 05 Oct 2017 20:18:23 GMT

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.