How to count distinct based on another column

[Applicable88]

Hello,

I have following table:

OrderID	Date		Value
A1	01.01.20		1000
A1	01.01.20		1000
B2	02.01.20		2000
B2	02.01.20		2000
B2	02.01.20		2000
C3	02.01.20		5000
C3	03.01.20		5000
D1	03.01.20		10000
E2	03.01.20		2000

I want to sum the Value of all Order ID only once. Since many other Order ID can also have the same Value, sum(distinct Value) cannot be used.

Second question: In my example every order with the same ORDER ID  also has same date.  Now I want to sum the value of only those with different Dates one time, and another one which only sum these with same dates, in case the table is full of different dates mixed. 

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(
    Sum(DISTINCT Value)
, OrderID))

For second question, you can try this

Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} Aggr(
    Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} DISTINCT Value)
, OrderID))

[Applicable88 · Accepted solution]

Hello Sunny,

thank you very much.

In the expression second example:

Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} Aggr(
    Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} DISTINCT Value)
, OrderID))

are there no Dollar Sign as a dollar sign expansion needed? Normally I saw that functions in a expression had a dollar sign. 

Best.

[sunny_talwar]

It is a search string, so no there is no dollar sign expansion here.

[Applicable88]

Sorry @sunny_talwar ,

I still don't get it fully. If its a search string, I guess the result of the count distinct date is a number?

{<OrderID = {"=Count(DISTINCT Date)

For example I get  4 distinct dates. How can Order ID equals 4? 

It doesnt make sense to me. Can you please explain it in easy words...

[sunny_talwar · Accepted solution]

It behaves like a true false statement... for each Order ID, if Count(DISTINCT Date) > 1, then true, else false. In the end the expression will pick all those Order ID which are true and leave false out. Does that make sense?

[EngMaher88]

Hello Sunny,

How can i make this calculation in the script part ? 

Thanks in advance.

[sunny_talwar]

Can you elaborate on what you are trying to do?