Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Count distinct values of a field based on distinct count of another field

Hi guys,

I have a FactOrderLine table where the Order_ID is repeated as many times as there are products ordered under the specific Order_ID. I want to count the distinct shipping dates based on the distinct count of Order_ID.

For example I have:

Order_ID, Shipping Date,ProductID

20;12/01/2018;P025

21;12/01/2018;P89

23;15/02/2018;P031

23;15/02/2018;P022

23;15/02/2018;P029

How can I set the expression in Qlik to have the distinct shipping date count based on the distinct count of the OrderID?  For Each Order ID I need to pick the count of one date and the date can be the same for another Order_ID.

If I set a COUNT DISTINCT on Shipping Date this will not be correct I assume as I can have two same dates but belongings to a different Order_ID.

How to do this by distinct count of Order_ID?

Thanks

Rick

 

 

 

Tags (1)
Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Count distinct values of a field based on distinct count of another field

Why not simply count Orders instead:

count(distinct OrderID) ?

Or, if you need to count multiple shipping dates per order, then count a combination of the two fields:

 

count(distinct OrderID & '|' & Date)

Cheers,

 

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Count distinct values of a field based on distinct count of another field

Why not simply count Orders instead:

count(distinct OrderID) ?

Or, if you need to count multiple shipping dates per order, then count a combination of the two fields:

 

count(distinct OrderID & '|' & Date)

Cheers,

 

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Count distinct values of a field based on distinct count of another field

Hi Oleg,

Counting Distinct OrderID only will not give me the distinct shipping date for distinct OrderID that have been shipped (there are also OrderID with not shipped status). 

I did a research and tried this expression :

Count(Aggr(shipping_date,orderID))

I am not sure this returns what I expect as a result. As far as I understood it will count the shipping date by orderID but I may use DISTINCT also to not return duplicates ?

Count(Aggr distinct(shipping_date,orderID)) 

How does your last expression work? it count distinct tuples of OrderID AND Shipping Date? 

Many thanks 

 

 

Highlighted
MVP & Luminary
MVP & Luminary

Re: Count distinct values of a field based on distinct count of another field

Hi,

 

the function AGGR() requires an aggregation function inside, and I doubt that you really need it...

The formula that I have suggested, counts all unique combinations of OrderID and Date.

If you just need to count Orders that have been shipped, you could add a condition that the order has been shipped - either using a status field, or using the field ShipDate. For example:

count( {<Status={'Shipped'}>}     distinct OrderID)

count( {<ShipDate={"=len(trim(ShipDate))>0"}>}     distinct OrderID)

Cheers,

Highlighted
Contributor II
Contributor II

Re: Count distinct values of a field based on distinct count of another field

Hi Oleg,

This is crystal clear thanks a lot :-).

So the formula I need here is the one you suggested : count(distinct OrderID & '|' & Date) which will return a unique set of OrderID and Date.

Many thanks and problem solved 🙂

Rick