table distribute, table distribute advance, table distribute accumulate, table distribute advance accumulate
This function facilitates applying the distribute() function on entire tables. It integrates all necessary preparations such as inserting vacant columns
for the results and does distribution work on every table row automatically, and all with one single function call. The function names ending with the
suffix accumulate will add up on values (rather than overwriting them) if the columns are already existing. In this case, specify softquoted
string '#existing' (single quotation marks are OK, but not double) in the 11th function parameter.
Note: Regarding dates, days are always considered as whole days, regardless if date functions come with a time of day information or not.
This function distributes a value over a time line of intervals such as days, weeks, months, quarters and years. Either a fixed total value is distributed
between two specified dates, or a combination of start date and capacity (unit per day) is provided and the end date will be calculated automatically
(e.g. Total value = 10, capacity per day = 2 means 5 days).
Doing a production planning with production start date, production end date and number of items to produce is a good example.
If a batch size is given (e.g. 10 items produced at a time), then specify it.
If the distribution should not be bound to deliveries, but to purchasing / receiving required material for these items, then use the function distribute advance.
Alternative to total value, '#days' (softquoted string, use no or single quotation marks) can be specified instead of the total value in order to return the number of days instead.
Detailed overview with many examples: See available PDF overview
Indirect parameter passing is disabled
9-15
No. | Type | Description |
---|---|---|
1. input |
string | Name of existing table |
2 input |
numeral or string | Total values If string: Existing header name in the table where the total values are located which need to be distributed. |
3 input |
numeral or string | Batch sizes If string: Existing header name in the table where the batch sizes are located which need to be distributed. |
4 input |
string date |
Start Dates If string: Existing header name in the table where the start dates are specified for every row. |
5 input |
string date numeral |
End Dates or capacity per day If string: Existing header name in the table where the end dates are specified for every row. |
6 input |
string | Intervals This parameter determinse if you want to do a plan on daily, weekly, monthly or other basis. Note that a daily planning is the most fine-grained
option and produces the biggest and most detailed plans.
|
7 input |
string | Usance Usance is a work and trade practice parameter which is used in association with the 5th function parameter Intervals.
|
8 input |
date or string converted to date | Output Start date This value specifies the starting date for the output to generate. If 5th parameter is not 'days', then the date will be moved back to the begin of the chosen interval (weeks, months, quarters, years). E.g. if '31.05.2017' is specified for months, then the whole month of May will be considered. |
9 input |
date or string converted to date | Output End date This value specifies the ending date for the output to generate. If 5th parameter is not 'days', then the date will be moved forward to the end of the chosen interval (weeks, months, quarters, years). E.g. if '01.05.2017' is specified for months, then the whole month May will be considered. |
Opt. 10 input |
set or string | Header date format Specify '' (single quotation marks) or {} (empty set) for standard date format YYYY-MM-DD |
Opt. 11 input |
table column | Column for distributed values Specify the column name or column number where to insert the distributed values. Use -1 (negative indexing is supported) to append the values on the right hand side
of the table.
|
Opt. 12 input |
table column | Earlier sum Specify at most 1 column header name or column number where to insert the values which lie before the range specified between output start date (8th function parameter).
See table columns as function parameters for general ruling for this parameter.
|
Opt. 13 input |
table column | Later sum Specify at most 1 column header name or column number where to insert the values which lie after the range specified between output end date (9th function parameter).
See table columns as function parameters for general ruling for this parameter.
|
Opt. 14 input |
set containing dates | Bank Holidays A set of dates may be supplied to declare specific days as bank holidays so these days will not be counted. This parameter is rejected if the Usance '30_360' is chosen.
The dates may be provided as data type date or string containing legitimate dates. Type conversion to dates takes place automatically.
|
Opt. 15 input |
set containing dates | School Holidays A set of dates may be supplied to declare school holidays (vacation days). This parameter is rejected if the Usance '30_360' is chosen.
The dates may be provided as data type date or string containing legitimate dates. Type conversion to dates takes place automatically.
An even number of dates must be specified as the first date describes the first vacation day and the next date describes the last vacation day.
|
Type | Description |
---|---|
boolean | Total value fully distributed true if all values have been distributed successfully into the date columns or columns designated for earlier sums or later sums as defined with the
12th and 13th function parameters. |
Batch size is negative, end date is earlier than start date, invalid combinations of intervals and usances.
table initialize( demo table,
{
{ Total Value, Start Date, End Date, Batch Size },
{ #days, 01.01.2017, 20.08.2017, 1 }, // Count number of days
{ 500, 01.01.2017, 20.08.2017, 0 }, // Produce 500 in 200 days (shifts on 7 days/week)
{ 500, 01.01.2017, 20.08.2017, 1 }, // Use whole numbers
{ 500, 01.01.2017, 20.08.2017, 10 }, // Deliver in batches of 10
{ 500, 01.01.2018, 20.08.2017, 10 }, // Skipped (end date is before start date)
// and causes return value to be 'false'
{ 500, 01.01.2017, 2 , 10 }, // Capacity: 2 per unit
{ 500, 01.12.2016, 2 , 10 } // Start a month earlier
} );
holidays[] = { 01.08.1900 }; // Public holiday every year
vacation[] = { 01.07.2017,31.07.2017 }; // Summer vacation
ret[] = table distribute( demo table, Total Value, Batch Size, Start Date, End Date,
months, calendar, '15.01.2017', '15.08.2017',Mmm,-1, Earlier, Later, holidays[], vacation[] );
// Note: For monthly intervals, output start date is moved back
// to 1st day and output end date to last day of month.
table list( demo table);
echo("return value = ", ret[] );
0 : Total Value | Start Date | End Date | Batch Size | Earlier | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Later
1 : #days | 01.01.2017 | 20.08.2017 | 1 | 0 | 31 | 28 | 31 | 30 | 31 | 30 | 0 | 19 | 0
2 : 500 | 01.01.2017 | 20.08.2017 | 0 | 0 | 77.5 | 70 | 77.5 | 75 | 77.5 | 75 | 0 | 47.5 | 0
3 : 500 | 01.01.2017 | 20.08.2017 | 1 | 0 | 77 | 70 | 78 | 75 | 77 | 75 | 0 | 48 | 0
4 : 500 | 01.01.2017 | 20.08.2017 | 10 | 0 | 70 | 70 | 80 | 80 | 70 | 80 | 0 | 50 | 0
5 : 500 | 01.01.2018 | 20.08.2017 | 10 | | | | | | | | | |
6 : 500 | 01.01.2017 | 2 | 10 | 0 | 60 | 50 | 70 | 60 | 60 | 60 | 0 | 60 | 80
7 : 500 | 01.12.2016 | 2 | 10 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 0 | 60 | 20
return value = false