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

[repetto74]

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

[Oleg_Troyansky · Accepted solution]

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,

[repetto74]

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 

[Oleg_Troyansky]

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,

[repetto74]

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