Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Stregge
Contributor II
Contributor II

New to Qlik: Need help with expression On time shipments

Hello Qlik Community!

I'm new to Qlik and having a problem.

I want to calculate number of shipments that are made on time and number of shipments that are late.

In my table I have ShipmentDate and DueDate (both in yyyy-mm-dd HH:MM:SS). 

For On time Shipment:  Shipment Date – Due Date <= 0

For late Shipment:    Shipment Date – Due Date > 0

I have tried various Sum and Count expressions, but I assume an if-expression is what I need?

How would such if-expression look like? I want to put this expression into a master measure!

Thanks in advance!

/Stregge

Labels (2)
2 Solutions

Accepted Solutions
jochem_zw
Partner Ambassador
Partner Ambassador

In expression:

Ontime: count(if(Shipment_date<=Due_Date,ShipmentNo))

Late: count(if(Shipment_date>Due_Date,ShipmentNo))

View solution in original post

jochem_zw
Partner Ambassador
Partner Ambassador

Shipmentno is field from table which will be counted

count(distinct ....... should do the trick 

View solution in original post

6 Replies
jochem_zw
Partner Ambassador
Partner Ambassador

the most easy option would be to create a flag field in the script.

So in your load statement create an extra field:

Load

Field1,

Field 2,

etc....,

if(Shipment Date<=Due Date,1,0)   As Flag_Shipment_OnTime

From ....................................;

when this is done, you could use count({$<Flag_Shipment_OnTime={1}>}ShipmentNo) for on time and count({$<Flag_Shipment_OnTime={0}>}ShipmentNo) for Late shipments

 

Stregge
Contributor II
Contributor II
Author

Hello Jochem,

Thank you for the reply!

I'm a bit confused since I'm new to this. I have not been working on the "scripting mode", but the drag and drop mode (and I've been told not to mix the two options)

Would there be an if-statement that would fit directly into the master measure expression (without attacking the script)?

Sorry if it's a bit confusing 🙂

jochem_zw
Partner Ambassador
Partner Ambassador

In expression:

Ontime: count(if(Shipment_date<=Due_Date,ShipmentNo))

Late: count(if(Shipment_date>Due_Date,ShipmentNo))

Stregge
Contributor II
Contributor II
Author

Thank you! I think it worked!

Would you mind explaining what 'ShipmentNo' does? Is that the name of a field? I replaced it with '0' and think I got it right.

I know the syntax of IF (condition, then,else), in this case 'ShipmentNo' equals 'then'?

 

Is there a way to get the distinct values of the expression? I've tried your expression with count distinct, but I get a syntax error.

Thank you again for your help!

jochem_zw
Partner Ambassador
Partner Ambassador

Shipmentno is field from table which will be counted

count(distinct ....... should do the trick 

Stregge
Contributor II
Contributor II
Author

Thank you, Jochem!

The problem is now solved and the count distinct worked very well.

Have a great day!

//Stregge