topic How to sum against unique ID in set expression in App Development

How to sum against unique ID in set expression

qs123 — Sat, 16 Nov 2024 05:23:36 GMT

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

Re: How to sum against unique ID in set expression

jwjackso — Wed, 03 Jul 2019 18:28:20 GMT

Would that be the same as Count(Distinct Order_ID)

Re: How to sum against unique ID in set expression

qs123 — Thu, 04 Jul 2019 16:07:31 GMT

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.

Re: How to sum against unique ID in set expression

qs123 — Sun, 15 Sep 2019 22:10:14 GMT

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