Can someone help me with the syntax?

damen

I am trying to get the percentages of each of our fields displayed in a table but I have to take column 1 (delinquent payment 2) and divide by column 2 (loan id)

when I run just the first line, I am able to get the correct calculation but I need to get all of the values calculated so I can display them on one table.

Any suggestions?

Here is the syntax I am using if that helps

sum(case when `Delinquent Payment 2` = '30 Days Delinquent' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = '60 Days Delinquent' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = '90 Days Delinquent' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = '120+ Days Delinquent' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = 'Bankruptcy 120+' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = 'Foreclosures' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = 'REO' then 1 else 0 end) / count (`Loan ID`)

sum(case when `Delinquent Payment 2` = 'Current' then 1 else 0 end) / count (`Loan ID`)

MarkSnodgrass

You need to make 8 different beast modes, 1 for each line you have in your screenshot. Then drag those 8 beast modes into your table card.

RobSomers

@damen You will want to do just a generic calculation and then use 'Delinquent Payment 2' as a column or series. Your beast mode would look like this:

SUM(1)/SUM(1)FIXED()

When you use this and a 'Delinquent Payment 2' column or series, Domo will do the grouping for you. The denominator of the beast mode will count the number of loans in each type of 'Delinquent Payment 2' and then the numerator will get the total number of loans in your timeframe.

damen

@RobSomers Ok so I pulled up the function video on Domos youtube and got a better sense. This seems to me to be the most logical answer but I'm only getting 1.00 as the output for this.

Basically, I need the % of loans that fall into each bucket. This table would normally work but I have to take out the "current total" row and only show ones that are actually delinquent. When I go to filter that row out, it takes and skews the percentages becasue 1300+ loans are no longer being accounted for. So, the formula for what I need is essentially, count of loan ID where delinquent payment = ____ / overall loan ID

We have this done in excel so I understand the math part of it but cant that same formula into Domo

RobSomers

@damen Use FILTER DENY inside the FIXED function in your numerator:

COUNT('Delinquent Payment 2`)FIXED(BY 'Loan ID' FILTER DENY 'Delinquent Payment 2')

When you exclude 'Current', the FILTER DENY will ignore that filter and keep counting the overall number of loans.

damen

@RobSomers

Still getting all 1.00 as the value input.

Not sure if I'm understanding you correctly on this. My apologies

Also, I did try your exact same syntax before I switched to count(loan id)

RobSomers

@damen Don't do the BY 'Loan ID' because then you're basically partitioning the data by Loan ID, so you're counting how many occurrences of that 'Loan ID' there are. Also wrap your denominator in an additional COUNT, so you have the following:

COUNT('Delinquent Payment 2')/COUNT(COUNT('loan id')FIXED(FILTER DENY 'Delinquent Payment 2'))

damen

That last one just shows its only giving me a count of all loan IDs that are in delinquency - for whatever reason it doesn't want to seem to do the division part of the problem.

@RobSomers