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]

Please review highlighted cell. For those rows calculation are going wrong...

Labels	Count of Delivery	OT (NEW Way)	If (NEW Way)	Sum of OTIF
BR,	392	386	0	6 Shouldn't this be 0
Vijay	761	749	760	748
NB2	9	0	4	5 Shouldn't this be 0
ZBPU	25	25	19	19
ZD2S	28003	28003	27991	27,991
ZKR	4604	1894	4150	2,004 Shouldn't this be less than or equal to 1894
TOTAL GRP1	33794	31057	32924	30773	91.90%	97.43%	91.06%
ZEU1	6705	1888	5122	2,577 Shouldn't this be less than or equal to 1888
ZIN1	254	3	201	56
TOTAL GRP2	6959	1891	5323	2633	27.17%	76.49%	37.84%
OR	760	717	305	298
UB	22393	20423	18901	17,677
TOTAL GRP3	23153	21140	19206	17975	91.31%	82.95%	77.64%

[joshrussin]

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

With this formula as Sum of OTIF, it checks to see if the OT has a value first.

[vvira1316]

Something I noticed in your original data file

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

That is not how we have it in data loader. please verify which one is correct?

[joshrussin]

To get the correct result in Qlik, I had to put what is in Qlik. I figured out why I am getting different values in Qlik from the Excel file.

Again these are the correct values.

I have been getting these values on QlikSense.

In Qlik, just based off of the raw data, it counts all of the deliveries including the items in them.

There were actually only 19 different deliveries.

So I did a DISTINCT and got the correct for the values in Count of Delivery. Now trying to figure out the Sum of OT, IF, and OTIF. The numbers are mostly correct, just not completely.

Now I just need to figure out how to make the other values distinct to the Count of Delivery and should be done!

[vvira1316]

great to know, good luck

Did you notice SaTy_OTIF value ZSET and ZRM are not part of wildmatch

[joshrussin]

Yes, my client doesn't need information on those types of shipments. Thank you for your help!

[joshrussin]

Hey Vijay,

For some reason, lately it has been doubling the Count of Delivery. It is throwing the OTIF way off. I could attach my current version and file to test out. Any way you could help look into this for me?

The OTIF Merged Data April 2018 file is the output file.

OTIF Merged Data file is the file for Qlik

[vvira1316]

Hi,

I hope you are doing well.

Sure. I'll take a look.

BR,

Vijay

[vvira1316]

Hi Joshua,

Can you please advise what duplication you are seeing?

One observation I just did whole table duplicate check in OTIF Merged Data Excel. It has some duplication, 69814 records are there currently but 41471 unique records out of those.

BR,

Vijay

[vvira1316]

Data Duplication is the reason you are seeing double count

Following is after removing duplicates and reloading data