Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qs123
Contributor II
Contributor II

How to sum against unique ID in set expression

Hi

In my data model, Order ID will repeat multiple time because 1 Order_ID may have more than 1 Item_Line. So all attributes associated with Order_ID are also repeating.

I want to do set analysis as below for Order Amounts but I also want it to count amount once per each unique Order_ID

 

Sum({ <[Order_Status]={'Pending'},[Order_Date] = {$(=Year(Today()))}, [Order_Date] = {$(=Year(Today()+1))}, [Order_Type]={'Online Services'}>} [Order Amount])

 

how to best write the above set analysis? so it will not double count?

 

Thanks

 

 

Labels (2)
1 Solution

Accepted Solutions
qs123
Contributor II
Contributor II
Author

I found the correct Expression

 

Sum(DISTINCT{ <[Order_Status]={'Pending'},[Order_Date] = {$(=Year(Today()))}, [Order_Date] = {$(=Year(Today()+1))}, [Order_Type]={'Online Services'}>}AGGR ([Order Amount],Order_ID))

 

Thank you everyone

 

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

Would that be the same as Count(Distinct Order_ID)

qs123
Contributor II
Contributor II
Author

NO, it will be different. Count(Distinct Order_ID) will only count unique order IDs

What I want is some of the order amount but i dont want it to recount the order amount twice if the Order ID row is repeating.

qs123
Contributor II
Contributor II
Author

I found the correct Expression

 

Sum(DISTINCT{ <[Order_Status]={'Pending'},[Order_Date] = {$(=Year(Today()))}, [Order_Date] = {$(=Year(Today()+1))}, [Order_Type]={'Online Services'}>}AGGR ([Order Amount],Order_ID))

 

Thank you everyone