Create a Letter Grade or Number Grade Based on Multiple Fields

guitarhero23
guitarhero23 Contributor
in Archive

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


**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Comments

  • Valiant
    Valiant Coach

    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
    guitarhero23 Contributor

    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.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Marc_H
    Marc_H Member

     

    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.

Categories