Hi Team,
I met some problems when summing the distinct assets. Say I have a table have three columns: userid, login_Date, assets. How can I get the total assets for all users which is 10k+5k+7.5k = 22.5k here? Thank you
| user id | login date | assets |
|---|---|---|
| 10000 | 20230101 | 10,000 |
| 10000 | 20230102 | 10,000 |
| 10001 | 20231002 | 5,000 |
| 10001 | 20230506 | 5,000 |
| 10001 | 20230603 | 5,000 |
| 10002 | 20230306 | 7,500 |
| 10002 | 20231101 | 7,500 |
Hello @mmmzzss and welcome to the Quicksight community!
In this example, is the user_id and the assets always going to be the same? For example, will the user_id 10000 always have 10,000 in the assets column?
If so, you could try the solution from this post:
Let me know if this helps!
Hi @mmmzzss
Can you try this?
sum(min({assets}, [{user id}]))
Hi duncan,
yes, the asset for each user is same but different across users. The post really helps! Thank you!
Hi David, yeah it works! Thank you so much!
What would be the solution for this problem if the user had two assets with different values?
| user id | login date | assets |
|---|---|---|
| 10000 | 20230101 | 10,000 |
| 10000 | 20230102 | 10,000 |
| 10001 | 20231002 | 5,000 |
| 10001 | 20230506 | 5,000 |
| 10001 | 20230603 | 3,000 |
| 10002 | 20230306 | 7,500 |
| 10002 | 20231101 | 7,500 |
The result using the proposed solution would be wrong:
sum(min({assets}, [{user id}]))
10000 = 10,000
10001 = 3,000
10002 = 7,500