Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

okamal99
New 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
Luminary
Luminary

Re: How to count unique time values

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)

7 Replies
Luminary
Luminary

Re: How to count unique time values

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)

mov
Esteemed Contributor III

Re: How to count unique time values

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

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

Luminary
Luminary

Re: How to count unique time values

-={''} excludes empty fields

={'*'} selects all values except NULL


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

mov
Esteemed Contributor III

Re: How to count unique time values

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
New Contributor II

Re: How to count unique time values

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

okamal99
New Contributor II

Re: How to count unique time values

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
New Contributor II

Re: How to count unique time values

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!