Scoring question

[#1]

I have a question about giving a score to my clients based on the following information:

1.  If the number of work orders in a 12 month period is greater than .60.  This is calculated by Number of Work Orders / Number of Students.  If true then they get 1 point.

2.  If the number of labor hours on work orders is greater than .60.   This is calculated by Total Labor Hours / Number of Work Orders.  If true then they get 1 point.

3.  So a client could have 1, 2, or 3 points depending on their results.

I have the following data points

1.  Organization Name

2.  Work Order Created Date.

3.  WOID

4.  Labor Hours

5.  Transaction Date for Labor Hours

Now what I would like to do is show their score over time.  So something that looks like:

Organization Name     Score 2002   Score 2003    Score 2004

A                                             1               2                    2         

I would like to run it for each year starting in 2002 - 2014.

Now I would like the 12 month number to based on Work order and Transaction Date starting with today and going back 12 months.  In other words:

2014 - Today - 12 months.

2013 - 12months ago - 12 months.

So on.

I would love to run this one time and get all the numbers instead of having to run a year at that time.  Does this make sense?  If so any ideas on how to begin.

[whiteline]

Hi.

Why don't you just calculate the scores in front-end ?

Something like this:

=rangesum(if(Count(distinct WOID)>60,1), if(Sum(Labor Hours)>60, 1))

For referencing the data based on rolling 12-moths you can use AsOfTable approach.

Calculating rolling n-period totals, averages or other aggregations

PS: How can a client get 3 points if you have only 2 conditions?

[#3 · Accepted solution]

Thank you so much.  You are correct about the points.  I was thinking about adding another score but didnt and forgot to update the text.  I will take a look at the link.