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: 
edugallowk
Contributor III
Contributor III

Sum and Count If or Count If and Sum

Hello my friends!

I need to add the value of users who fall into the following criteria.

I need to count the number of events and divide by the number of months / year. If the result is equal to or greater than 1 I need to add the value of these users.

number of events:
count (if (events = 1 or events = 2), cod_events)

amount of month / year
count (distinct data)

I check if users have an average equal to or greater than one:

count (if (events = 1 or events = 2), cod_events) / count (distinct data)

so I find the users who are in this condition the script below:

 

count(distinct if(aggr(
(count(if([Events]=1 or [Events]=11, [Cod_Events]))
/
count(distinct [Date])) > 1
, username), username))

 

Now using the same code structure, I can not get the amount spent from these users.

😞

 

SUM(if(aggr(
(count(if([Events]=1 or [Events]=11, [Cod_Events]))
/
count(distinct [Date])) > 1
, money), username))

 

can anyone give me a hint how to structure this script better so that it returns the correct value?

 

Tks.

Labels (6)
1 Reply
edugallowk
Contributor III
Contributor III
Author

I tried this and also did not return the correct value.

sum(aggr(
If(count(if([cod_event]=1 or [cod_event]=11, [cod_event])) 
/ 
count(distinct [data_real]) >1, [valor_money])
, cod_user, cod_user))