Can someone help me with the syntax?

damen
damen Contributor
edited April 2023 in Beast Mode

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`)

If this helps, feel free to agree, accept or awesome it!

Tagged:

Answers

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

    **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.
  • RobSomers
    RobSomers Coach
    edited October 2022

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen Contributor

    @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



    If this helps, feel free to agree, accept or awesome it!

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen Contributor
    edited October 2022

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

    If this helps, feel free to agree, accept or awesome it!

  • RobSomers
    RobSomers Coach
    edited October 2022

    @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'))

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen Contributor

    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

    If this helps, feel free to agree, accept or awesome it!