Help with the SUM function, I get "An issue has occurred during processing"

andresperezfc

I am going to try to explain myself better, what I am trying to do is identify how many times a person appears in a dataset, in my particular case I need to know the number of people who have created more than 100 contents

when trying to run this function, this function tries to count the users that appear or have more than 100 records in the dataset

SUM(CASE WHEN (COUNT(UserId) > 100) THEN 1 ELSE 0 END)

for example, in this dataset

the user Andrés has created 8 contents
Carlos has created 4 contents
David has created 4 contents
Julieta has created 6 contents
Maria has created 5 contents
Roberto has created 2 contents

The formula that I am trying to do in beast Mode, is to know how many users have created, for example, more than 5 contents, the output should be 2, because only Juliet and Andrés have created more than 5 contents

GrantSmith

You'll need a window function to aggregate an aggregate and a count distinct to count the users only once.

COUNT(DISTINCT CASE WHEN COUNT(`userid`) OVER () > 100 THEN `userid` END) 

Godiepi

Hola @andresperezfc , espero que esto te ayude…

Looking at the description of what you want to accomplish, it sounds to me like you need just a table that lists the users per dataset that have created more than 100 contents.

You can create a table card as this:

• Column 1 = Dataset Name or Dataset ID
• Column 2 = UserID
• Column 3 = Name
• Column 4 = idcontent aggregated as Count and labeled as 'Content Created'
• on the sorting settings bring the idcontent field as sort descending aggregated as Count
• on the filter settings bring the idcontent field again and in the selection type use 'Aggregation' and 'Is Greater Than' 100

This settings will make your table to display only the names of users that have created more than 100 per dataset

andresperezfc

https://community-forums.domo.com/main/discussion/comment/64744#Comment_64744

Thank you for answering, I tried to follow your suggestion but I still get "An issue has occurred during processing" 😓

I don't understand why, do you know any other way to do this?

andresperezfc

https://community-forums.domo.com/main/discussion/comment/64745#Comment_64745

Thanks for answering, of course I could do that, in fact I did it but it's not what I need, because I need to show the number of people who have created more than 100 contents on a card as an individual value

Do you know any other way to do it?

Godiepi

I see,

In my opinion you should do it in a Magic ETL

• Select columns for a list of all Datasets , UserID and Content
• Group by Datasets, UserID and Aggregate COUNT(Content) AS Total_Content_Created
• Add a Formula tile to flag the UserID on a particular Dataset where they have more than 100 Total_Content_Created to equal 1 else 0
• Join the results back to the dataset on Dataset and UserID (This way a user that has created content in multiple dataset , will be only tagged in the one that have created more than 100
• with the Flag already in your main dataset , use it as a filter in your single number card and do a simple COUNT(Distinct UserID)

ST_-Superman-_

It seems like you should be able to just use

COUNT(DISTINCT `idcontent`) 

Then you can set that value as a filter and select >5 or >100, whatever you want to filter for. Then, just include UserId and Name in the table.