Group by when using case statements?

user046467

I'm trying to group the column 'opzegreden' into one line if the sum is below 5.

I'm using the following beastmode which gives me not the desired result (see screenshot)
case when sum(Aantal) <= 5 then 'Why' else Opzegreden end

If I use the following beastmode then it does it work and does group..

case when Opzegreden like '%no time%' then 'why'
when Opzegreden like '%price%' then 'why'
else Opzegreden end

I'm not using any filters or sorting.

GrantSmith

What is the structure of your data? What type of visualization are you utilizing? What do you want the output data or visualization to look like?

You may need to utilize a window function to calculate the category.

CASE WHEN SUM(SUM(`Aantal`)) OVER (PARTITION BY `Your Grouping Columns`) <= 5 THEN 'Why' ELSE `Opzegreden` END

user046467

https://community-forums.domo.com/main/discussion/comment/64846#Comment_64846

Thanks for your reply @GrantSmith

Sadly that doesn't seem to work. Probably because my explination of the case was lacking.

I've a list of cancellation reasons ('opzegreden').

• reason 1 : 15 people
• reason 2 : 10 people
• reason 3 : 7 people
• reason 4 : 3 people
• reason 5: 2 people

But I want to group the reasons that have less than 5 people because it clutters my data.
So instead of reason 4 & 5 I want a row with grouped reasons.

• reason 1 : 15 people
• reason 2 : 10 people
• reason 3 : 7 people
• grouped reasons 4 : 5 people

But instead it gives me:

• reason 1 : 15 people
• reason 2 : 10 people
• reason 3 : 7 people
• grouped reasons 4 : 3 people
• grouped reasons 4: 2 people

In this case I only have this beastmode as a column and one which counts the amount of people that cancelled in that category. (I'm using a donut graph if that matters anything)

Hopefully it's clear now what I'm trying to accomplish.

user046467

@GrantSmith Is this possible in the case I described above? :)

GrantSmith

This won't work in a beast mode as you can't aggregate (sum of total per reason) an aggregate (total per reason). You'd need to pre-aggregate the data in an ETL first and then do your final grouping in the card.

marcel_luthi

@user046467 can you post a sample of what the dataset you have looks like? As Grant said, you can do a lot with Windows functions (or the Fixed function) but there are limitations for which pre-aggregating needs to happen at a ETL level, but without fully understanding what your data looks like, we're mostly guessing in the dark. (I fear there might be an easy answer we might be overlooking due to lack of insights on the data)