Help with the SUM function, I get "An issue has occurred during processing"
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
Answers
-
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)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
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
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
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?
0 -
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?
0 -
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)
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive