Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
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
Highlighted
Specialist II
Specialist II

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

   

LabelsCount of DeliveryOT (NEW Way)If (NEW Way)Sum of OTIF
BR,39238606


Shouldn't this be 0

Vijay761749760748
NB2904

5

Shouldn't this be 0

ZBPU25251919
ZD2S28003280032799127,991
ZKR460418944150

2,004


Shouldn't this be less than or equal to 1894

TOTAL GRP13379431057329243077391.90%97.43%91.06%
ZEU1670518885122

2,577


Shouldn't this be less than or equal to 1888

ZIN1254320156
TOTAL GRP2695918915323263327.17%76.49%37.84%
OR760717305298
UB22393204231890117,677
TOTAL GRP32315321140192061797591.31%82.95%77.64%
Highlighted
Creator III
Creator III

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.

Highlighted
Specialist II
Specialist II

Something I noticed in your original data file

Formula.PNG

Formula2.PNG

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?

Highlighted
Creator III
Creator III

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.

KPI_0028.jpg

I have been getting these values on QlikSense.

KPI_0029.jpg

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.

KPI_0030.jpg

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

Highlighted
Specialist II
Specialist II

great to know, good luck

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

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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

Highlighted
Specialist II
Specialist II

Hi,

I hope you are doing well.

Sure. I'll take a look.

BR,

Vijay

Highlighted
Specialist II
Specialist II

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

Highlighted
Specialist II
Specialist II

Data Duplication is the reason you are seeing double count

JRInfo.PNG

Following is after removing duplicates and reloading data

JRInfo2.PNG