Create a Letter Grade or Number Grade Based on Multiple Fields

guitarhero23

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? 

 

Example:

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

Valiant

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.

 

Possible Example:

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

      SELECT

                A.Customer,

                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?

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

guitarhero23

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. 

 

Ex.

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.

Marc_H

 

Hi, GuitarHero,

 

I might be simpilfying this question, but would nesting your formula in a CASE statement give you what you need?

 

Best wishes,

 

 

 

Marc H.