Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

In expression:

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

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

View solution in original post

Highlighted
Partner
Partner

Shipmentno is field from table which will be counted

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

View solution in original post

6 Replies
Highlighted
Partner
Partner

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

 

Highlighted
Contributor II
Contributor II

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 🙂

Highlighted
Partner
Partner

In expression:

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

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

View solution in original post

Highlighted
Contributor II
Contributor II

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!

Highlighted
Partner
Partner

Shipmentno is field from table which will be counted

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

View solution in original post

Highlighted
Contributor II
Contributor II

Thank you, Jochem!

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

Have a great day!

//Stregge