Create a Letter Grade or Number Grade Based on Multiple Fields
I'm going to experiement with using the calculated field to create what I might call a Health Grade for our customers.
Short Context. People can send us data. The data can process correctly or a % might fail. I summarize this % per month for each account.
I would like to do something like taking the %fail data and combining it with other criteria and the outcome is some sort of health grade, each field being weighted some TBD amount compared to eachother. Has anyone done something similar and can provide an example or some ideas?
- %fail = .70
- PaymentLateDays = 5
- Field 3
- Field 4
Letter grade = C due to the 70% failure, they are 5 days late on payment, and some other 2 fields. Also interested in having some be a measure over time. Such as 2 months in a row over 70% failure or something like that
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
A lot of this is going to stem from how exactly you're calculating the % Fail? For example if its # of processed rows of data / # of unprocessed rows of data then you could do Counts by Customer for each of those and divide them in a summation transform.
Transform 1 - Count of Unprocessed Rows by Customer
Column1 - Customer
Column2 - Count(*) as Count1
Tranform 2 - Count of Processed Rows by Customer
Column1 - Customer
Column2 - Count(*) as Count2
Tranform 3 - Combine Metrics
B.Count2/B.Count1 AS Fail_Perc,
CASE WHEN B.Count2/B.Count1 BETWEEN 0 AND .7 THEN 'F'
WHEN B.Count2/B.Count1 BETWEEN .7 AND .8 THEN 'C'
WHEN B.Count2/B.Count1 BETWEEN .8 AND .9 THEN 'B'
WHEN B.Count2/B.Count1 > .9 THEN 'A'
END AS Grade
FROM Transform1 as A LEFT JOIN Transform2 as B ON A.Customer = B.Customer
And then you could join that dataset to whatever was holding your other metrics by customer.
Is this what you were thinking or did you have something else in mind?
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2
This is along the lines, you've given me some ideas.
From the data itself in SQL Server we basically get a success number and a rejected number for each row of data because not all of the data fails for each run through the system. Basically if 100 things come in as one 0-100 could fail so i calculate that failure in Domo in a calculated field right now as failed/success+failed.
I'd like to do something like taking that number and assigning it a weight for how important I think that is towards this "Health Grade" or whatever I call it. So like you showed I could taken ranges and say if the fail% is between 0-20% then the value is X, 21-50 = Y, and so on.
Fail% is worth 30% out of 100 of this made up Grade
Column 2 is worth 25% out of 100
Column 3 is worth 25% out of 100
Column 4 is worth 20% out of 100.
So based on the fail% it would get somewhere between 0-30 points. Same for the others.
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0
I might be simpilfying this question, but would nesting your formula in a CASE statement give you what you need?
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 88 Cards, Dashboards, Stories
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive