Need help writing a Beastmode

Ryan_Heath00

This is my first BeastMode to create and having some trouble. I am probably way off so just wanted to ask here to save some time. What I am trying to accomplish is to get a sum of current balance of those that are in the groups above 120 Days in the age group column. I'm wanting to Name the Column 120+

Here is my syntax

CASE WHEN `Age group` IN ('121-180 Days','181-270 Days','271-365 Days','366-730 Days','731  Days+') THEN (SUM(`CurrentBalance`) as `120+`) END

Am I anywhere close?

Ryan_Heath00

I had to change it up ever so slightly and finally got it. I have the 120+ column at the end displaying what I need, but It is also a part of all the other column groupings.

Thank you both for taking the time to help me figure things out. It might be time to start playing with ETL or SQL to see if I might can fine tune things.

ArborRose

In beast mode, CASE statements can’t perform aggregate functions like SUM within the conditional logic. Use the CASE statement to identify the rows that meet your criteria, and then apply the SUM function on that logic at the card level.

I'm not sure what you are trying to do with your conditional logic. You are saying if the age group is A, B, or C then do something. Normally you would have a case WHEN for each or some of those age groups. From what I see, you are asking for current balance on any age group over 120 days.

CASE 

    WHEN `Days Past Due` > 120 THEN `CurrentBalance`

    ELSE 0 

END

Create the formula, and then aggregate (SUM) on the card.

Ryan_Heath00

Here is a better view. What I am needing is only certain values from the age group. Im wanting to ignore the groups of 120 and below. They are wanting current balance for anything in groups that are over 120 days as a new column called `120+` Below this pic is a pic of a spreadsheet example she showed me.

I am needing to create the orange column.

GrantSmith

Try putting your aggregation outside of your case statement

SUM(CASE WHEN `Age group` IN ('121-180 Days','181-270 Days','271-365 Days','366-730 Days','731  Days+') THEN `CurrentBalance` END)

Ryan_Heath00

Unable to get anything to work using both of yalls advice. I don't know if what she is wanting is possible using beastmode.

ArborRose

The formula as Grant has typed it looks correct. Assuming your typing exactly matches the condition logic.

Using a fake dataset:

hospital_name	current_balance	financial_class	aging_group
Central Hospital	15000	Medicare	0-30 days
Central Hospital	22000	Medicare	31-60 days
Westside Clinic	5000	Medicaid	61-90 days
Eastside General	30000	Private	91-120 days
Eastside General	10000	Medicare	121-180 days
Westside Clinic	7000	Private	181-270 days
Central Hospital	4000	Medicaid	271-365 days
Central Hospital	8000	Private	366-730 days
Eastside General	1500	Medicare	731+ days
Westside Clinic	32000	Medicaid	0-30 days
Eastside General	24000	Private	31-60 days
Central Hospital	2000	Medicare	61-90 days
Westside Clinic	4500	Private	91-120 days
Eastside General	21000	Medicaid	121-180 days
Central Hospital	17000	Medicare	181-270 days
Eastside General	14000	Medicaid	271-365 days
Westside Clinic	11000	Private	366-730 days
Central Hospital	27000	Medicare	731+ days

If I throw this on a pivot table and try to guess at what you have on yours.

and also throw the test calculation at the end to see if it calculates correctly…it is aggregating the proper values that I show highlighted in yellow.

Ryan_Heath00

I had to change it up ever so slightly and finally got it. I have the 120+ column at the end displaying what I need, but It is also a part of all the other column groupings.

Thank you both for taking the time to help me figure things out. It might be time to start playing with ETL or SQL to see if I might can fine tune things.