2 Replies Latest reply: Oct 8, 2012 10:35 AM by Byron Schuurman

# Only multiply when count is lager than 1

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
42343824342528
63463824342634
55534824342752
45474824342834
41246824342834
86742824342939
32156824343028
12567824343052
86723824343128

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.

• ###### Re: Only multiply when count is lager than 1

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

• ###### Re: Only multiply when count is lager than 1

Indeed, this was the correct expression:

`Sum({< user.id = {"=Count(usersubscription.id) > 1"} >} subscription.amount)`

Thank you very much!