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

andresperezfc
andresperezfc Member
edited April 2023 in Magic ETL

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!**
  • Godiepi
    Godiepi Coach
    edited April 2023

    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'
  • 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?

  • 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
    Godiepi Coach
    edited April 2023

    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'
  • 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.” -Superman