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

andresperezfc

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

MarkSnodgrass

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.

andresperezfc

@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

@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.

MichelleH

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

andresperezfc

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

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

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

MichelleH

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

andresperezfc

is a single value