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

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

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

Historical functions for historical fields

The equation here will calculate out the historical sum of the data from Sunday-Saturday, AND today's data (f it is in that date range).

 

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

E-Mail me when someone replies to this comment