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: 
Not applicable

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.

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

2 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

Indeed, this was the correct expression:

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

Thank you very much!