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: 
okamal99
Contributor II
Contributor II

How to count unique time values

Hi guys,

I'm fairly new to Qlik Sense.  I'm trying to determine unique time counts.  I have freight bills that either have one time of delivery, multiple times of delivery or no time of delivery.

I want to count unique times of delivery for each freight bill.

The fields would be as follows:

BILL_NUMBER (which is the freight bill)

COMPLETED TIME( which is the delivery time)

Eg. if the freight bill 123 has a delivery time of 1:00:00 and another delivery time of 2:00:00, it only calculates it as 1 timestamp and not 2.  Also, if there is no delivery time, then it counts it as 0.

What expression can I write so that each freight bill only counts the first time of delivery and then sums up all the unique times for all the freight bills?

Any help would be greatly appreciated!

Thanks

Osman

1 Solution

Accepted Solutions
Ivan_Bozov
Luminary
Luminary

Hi! If I understand your requirement correctly, you want to count each freight bill only once:

Count(Distinct BILL_NUMBER)

Share a sample of your data if this does not help.

Edit: I see that there might be no delivery time entered and you don't want to count this bills. Then try this:

Count({<[COMPLETED TIME]-={''}>}Distinct BILL_NUMBER)

vizmind.eu

View solution in original post

7 Replies
Ivan_Bozov
Luminary
Luminary

Hi! If I understand your requirement correctly, you want to count each freight bill only once:

Count(Distinct BILL_NUMBER)

Share a sample of your data if this does not help.

Edit: I see that there might be no delivery time entered and you don't want to count this bills. Then try this:

Count({<[COMPLETED TIME]-={''}>}Distinct BILL_NUMBER)

vizmind.eu
Anonymous
Not applicable

This might work, depending on data, but a more reliable would be:

Count({<[COMPLETED TIME]={'*'}>}Distinct BILL_NUMBER)

Ivan_Bozov
Luminary
Luminary

-={''} excludes empty fields

={'*'} selects all values except NULL


So both should provide the same result if the delivery time field is empty.

vizmind.eu
Anonymous
Not applicable

Not exactly...

'' is a blank text, it is not the same as null, although it "looks" the same in many cases.  So

-={''} excludes empty fields but not nulls

={'*'} selects all values that has data, hence both nulls and blanks are excluded.


Hope it helps

mukesh19
Contributor II
Contributor II

Count({<[COMPLETED TIME]={'*'}>}Distinct BILL_NUMBER)

okamal99
Contributor II
Contributor II
Author

Hi Ivan,

Wow it works!! You're a genius:) I got a result of 20,181 which is the exact result I was looking for.

Thanks so much for your help!

okamal99
Contributor II
Contributor II
Author

Hi Michael,

Your expression works too! You are definitely a legend:)  Thanks for the explanation also between yours and Ivan's expression.

Again, thanks for your help!