How to count total products for each distinct order? with condition

[#1]

Hi Guys,

My in my case one OrderId may repeat more then once and one Order may have more then one product.

About the Data Model :

Due to left joins both (OrderID and ProductID) do repeat couple of times.

Keep in mind, one order can not contain duplicate products. If the order has more then one product then they must be different products.

My fields are : [OrderId] & [ProductId]

How should I count total and avg (distinct) products ordered per order?

And how to add if condition in that expression like if [OrderDate] = Date(Today()) ?

Message was edited by: Lokesh Patel Second question added for if condition

[MK_QSL]

Avg(Aggr(COUNT(Distinct ProductID),OrderID))

or

Avg(TOTAL Aggr(COUNT(Distinct ProductID),OrderID))

and

SUM(Aggr(COUNT(Distinct ProductID),OrderID))

or

SUM(TOTAL Aggr(COUNT(Distinct ProductID),OrderID))

[#3]

thanks, but just for knowledge.. what is the difference in both expressions?

[MK_QSL]

Depends on where you are using..

If you use table, you have to use TOTAL...

If you use text box, you can use without TOTAL

[#5]

Thanks and where can I add an if condition in that expression if i want to see only those orders where OrderDate = Date(Today())

[MK_QSL · Accepted solution]

Avg({<OrderDate = {'$(=Date(Today()))'}>}Aggr(COUNT({<OrderDate = {'$(=Date(Today()))'}>}Distinct ProductID),OrderID))