Last Custom Historical Calculations that calculate over custom ranges
When you enter in a historical calculation, you may want it to total the data over a custom weekly range. For example Sunday to Saturday
You can now use custom date ranges to calculate out a sums, mean, or minimum data over a specific date range.
It is important that you use the correct historical calculations and functions when you write the equations in. Most users will want to use the historicalsum (historical field, field) type equations, as opposed to sum(historical field). The steps below outline the differences.
You MUST ensure that you understand the difference between using a Historical Field or, using a historical function and a Historical Field
The image in the example here shows that all of the last custom week date restrictions have been set up to calculate Historical workload as
e.g sum(Historical Workload)
Last Custom Sun-Sat
Last Custom Mon-Sun
Last Custom Tues-Mon
Last Custom Wed-Tues
Last Custom Thurs-Wed
Last Custom Fri-Thurs
Last Custom Sat-Friday
The MAIN limitation you need to know about when using the sum of a historical field, is that data will ONLY calculate for historical data. The data entered on the day you are entering in the data will NOT be included in the calculation.
The equation e.g sum(Historical Workload) for Last Custom Sun-Sat will calculate the load from the last Sunday to following Saturday. If you were entering in new data on a Sunday, that day will not calculate, because you are starting a new week, and the historical calculations only display data for the previous day (which would be the Saturday), as shown in the image two steps below.
If you want to include the current day that you are entering into the equation, then you need to use a historicalsum calculation and add in today's data (as shown in the image in the step below).
Historical functions for historical fields
The easiest way to see how the field calculate is referring to the results
sum(Historical Workload) fields
In the example here you can see that using the sum(Historical Workload), the calculations do not calculate until a day after the first entry, e.g. for all historical calculation entered on the 01-07-2013 all historical calculations are blank. This is because they only calculate out historical data, not the date entered today. This means, at the start of each week (e.g. Sun, Mon, Tues, Wed, Thu, Fri, Sat, Sun) these values are also blank because they cannot include data from the previous week, but they also do not include today's data.
historicalsum(Historical Workload, Workload) fields
In the example here, the two fields using this equation calculate the historical data, AND the data entered for today.This means they start calculating from the 01-07-2013, and they also correctly display the data at the start of the custom week range, so there are no blank values.
This is the type of equation most users will require.
If you are using a last custom week range, you MUST understand the difference between the sum and the historicalsum functions for Historical calculations
Always set up a test case and enter in enough data to check that your custom ranges are working as required.
0 Comments
Add your comment