Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
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
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,
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