# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for
Did you mean:
Creator III

## Pivot table help

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

30 Replies
Specialist II

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 6Shouldn't this be 0 Vijay 761 749 760 748 NB2 9 0 4 5Shouldn't this be 0 ZBPU 25 25 19 19 ZD2S 28003 28003 27991 27,991 ZKR 4604 1894 4150 2,004Shouldn'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,577Shouldn'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%
Creator III
Author

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.

Specialist II

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?

Creator III
Author

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!

Specialist II

great to know, good luck

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

Creator III
Author

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

Creator III
Author

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

Specialist II

Hi,

I hope you are doing well.

Sure. I'll take a look.

BR,

Vijay

Specialist II

Hi Joshua,

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

Specialist II

Data Duplication is the reason you are seeing double count