topic Re: Only multiply when count is lager than 1 in QlikView

Only multiply when count is lager than 1

Mon, 08 Oct 2012 14:14:58 GMT

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.

Re: Only multiply when count is lager than 1

Miguel_Angel_Baeyens — Mon, 08 Oct 2012 14:22:06 GMT

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

Mon, 08 Oct 2012 14:35:09 GMT

Indeed, this was the correct expression:

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

Thank you very much!