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

BlueRooster

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.