How to count unique time values

[okamal99]

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

[Ivan_Bozov · Accepted solution]

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)

[Anonymous]

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

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

[Ivan_Bozov]

-={''} excludes empty fields

={'*'} selects all values except NULL

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

[Anonymous]

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]

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

[okamal99]

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]

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!