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

andresperezfc
andresperezfc Member
in Magic ETL

An issue has occurred during processing. We are unable to complete the request at this time.

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)

I appreciate any help or opinion

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    You can't do an aggregate on aggregate without using an OVER clause (window function) or the new fixed functions. Here is the KB article on fixed functions.
    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    Another option is to only have this in your beast mode:

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

    Then once your drag into the field you want on your card, choose the aggregation type of sum.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • andresperezfc
    andresperezfc Member
    edited April 2023

    @MarkSnodgrass Hello, thanks for answering

    I tried to follow your recommendations but I had no success, when dragging the calculated field created with Beast Mode I can't choose the aggregation type of sum.

    Do you think there is another way to do it?

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @andresperezfc -

    The general formula for a window function in Domo would look something like this:

    • SUM(SUM( `fieldName` )) OVER (PARTITION BY `seriesName`)

    You can allso use FIXED functions

    • SUM(SUM( `fieldName` )) FIXED (BY `seriesName`)

    That being said, you generally want to try to avoid nesting window functions within a calculated field. My initial suggestion here would be to have a dataflow calculate what you are looking for. Happy to help with that if you want to go down that road.

    If we try to apply that fixed or window formula to your case, what are we partitioning the records by? You want to count how many times you had more than 100 userid's. But is that in a month? Or is it 100 userid's from a zip code, or some other field?

    SUM(COUNT(`userID`)) FIXED (BY `zipCode`)

    That would give you the number of userID's in a given zip code. To then count how many zip codes have more than 100 users I would try something like this:

    COUNT(DISTINCT CASE WHEN SUM(COUNT(`userID`)) FIXED (BY `zipCode`) > 100 THEN `zipCode` END)

    I'm not sure if Domo would like the nested window statement though.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • MichelleH
    MichelleH Coach

    @andresperezfc Can you please send a screenshot of the available aggregation types for this field?

  • andresperezfc
    andresperezfc Member
    edited April 2023

    In fact I can't choose any other type :(

  • andresperezfc
    andresperezfc Member

    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

    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

    image.png

  • MichelleH
    MichelleH Coach

    @andresperezfc How do you current have your card set up in analyzer (i.e. chart type, filters, etc)

  • andresperezfc
    andresperezfc Member

    is a single value

    image.png

Categories