Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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?
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.