Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How to count distinct based on another column

Hello,

I have following table:

 

OrderIDDate Value
A101.01.20 1000
A101.01.20 1000
B202.01.20 2000
B202.01.20 2000
B202.01.20 2000
C302.01.20 5000
C303.01.20 5000
D103.01.20 10000
E203.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.

 

3 Solutions

Accepted Solutions
Highlighted

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))

View solution in original post

Highlighted
Creator II
Creator II

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.

View solution in original post

Highlighted

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?

View solution in original post

5 Replies
Highlighted

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))

View solution in original post

Highlighted
Creator II
Creator II

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.

View solution in original post

Highlighted

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

Highlighted
Creator II
Creator II

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...

Highlighted

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?

View solution in original post