Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a fairy simpel problem for which is still don't have a correct solution. A user with a user.id can subscribe to one or more subscriptions. All purchased subscriptions are present in a usersubscription table that has a unique usersubscription id and corresponding user ids (usersubscription.id, user.id). The total revenue is caculated by multiplying the amount of a subscription (subscription.amount) with count(usersubscription.id).
So, I have the following straight table which is sorted by user.id:
usersubscription.id | user.id | revenue (subscription.amount * count(usersubscription.id) |
---|---|---|
Total = 31253 | ||
42343 | 8243425 | 28 |
63463 | 8243426 | 34 |
55534 | 8243427 | 52 |
45474 | 8243428 | 34 |
41246 | 8243428 | 34 |
86742 | 8243429 | 39 |
32156 | 8243430 | 28 |
12567 | 8243430 | 52 |
86723 | 8243431 | 28 |
Now I only want to calculate the revenue if there are more than 1 user.id, so in this case the user.ids that are bold. I've tried numerous expressions, with e.g. aggregated count, but with no success. I think there is a very simpel solution for this, but I can find it. 😉
Can anyone help? Thanks in advance.
Hi,
Something like this should work, using set analysis and a function to narrow down the data set use to sum values.
Sum({< user.id = {"=Count(user.id) > 1"} >} subscription.amount)
That reads like "sum the values in field subscription.amount for those user.id that have Count() greater than 1".
Hope that helps.
Miguel
Hi,
Something like this should work, using set analysis and a function to narrow down the data set use to sum values.
Sum({< user.id = {"=Count(user.id) > 1"} >} subscription.amount)
That reads like "sum the values in field subscription.amount for those user.id that have Count() greater than 1".
Hope that helps.
Miguel
Indeed, this was the correct expression:
Sum({< user.id = {"=Count(usersubscription.id) > 1"} >} subscription.amount)
Thank you very much!