Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In expression:
Ontime: count(if(Shipment_date<=Due_Date,ShipmentNo))
Late: count(if(Shipment_date>Due_Date,ShipmentNo))
Shipmentno is field from table which will be counted
count(distinct ....... should do the trick
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
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 🙂
In expression:
Ontime: count(if(Shipment_date<=Due_Date,ShipmentNo))
Late: count(if(Shipment_date>Due_Date,ShipmentNo))
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!
Shipmentno is field from table which will be counted
count(distinct ....... should do the trick
Thank you, Jochem!
The problem is now solved and the count distinct worked very well.
Have a great day!
//Stregge