Can someone help me with the syntax?

damen · 2022-10-21T20:21:56+00:00

There was an error rendering this rich post.

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

Quick Links

Accepted answers

All comments

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

other categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.

Find more posts tagged with

Beast Mode