table distribute ...

Prev Next

Function Names

table distribute, table distribute advance, table distribute accumulate, table distribute advance accumulate

Description

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

Call as: procedure or function

Restrictions

Indirect parameter passing is disabled

Parameter count

9-15

Parameters

No.TypeDescription
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.
If numeral: This will be the generalized total value applicable for all table rows.

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.
If numeral: This will be the generalized batch size applicable for all table rows.

4
input
string
date
Start Dates

If string: Existing header name in the table where the start dates are specified for every row.
If date: This will be the generalized start date applicable for all table rows.

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.
If date: This will be the generalized end date applicable for all table rows.
If numeral: Capacity (number of units) per day, applicable for all table rows.

Attention: In case a table column for end dates is specified, then only the table rows containing valid end dates as well as end dates not earlier than start dates or valid capacity per day values will be processed. Otherwise, the rows will be skipped. No exceptions (error messages) are asserted. This feature may be useful to skip specific rows intentionally.

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.
Valid values are: days, weeks, whole weeks, months, whole months, quarters, whole quarters, quarters whole moths, years, whole years, years whole months, years whole quarters.
Theese values are exaplined listed in the 5th parameter of the function distribute().

7
input
string Usance

Usance is a work and trade practice parameter which is used in association with the 5th function parameter Intervals.
Valid values are: calendar, mon_fri, mon_sat, 30_360.
Theese values are exaplined listed in the 6th parameter of the function distribute().

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
Specify 1 value (1 element in set, or 1 string) to specify the formatting template as specified in numeral to string conversion and formatting.
Specify 2 values (set or softquoted string, values separated by comma) where the 2nd value is the locale value (formatting option), (exmaple: en_US).
Specify 3 values (like above) where the 3rd value is the placeholder symbol, also defined in numeral to string conversion and formatting.

Default value: {} (not specified)
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.
Attention: Column insertion takes place regardless if the column with same date line names do already exist or not. All other columns further to the right will be shifted accordingly to avoid overwriting existing contents. Exception to the rule: If the softquoted string '#existing' (use no quotation or single quotation marks, but not double quotation marks) is specified, then B4P looks for the existing column names along the time line. The existing columns must be in the same sequence as the new ones being created. This allows you to call the function table distribute followed by table distribute accumulate on the same table where the distributed values are added up. See table columns as function parameters for general ruling for this parameter.
Specific rules apply for this function:

  • Only 1 header name or column number may be specified.

Default value: -1 (append the columns after the right-most column)
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.
Specific rules apply for this function:

  • At most 1 header name or column number may be specified.

Default value: {} (no column specified)
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.
Specific rules apply for this function:

  • At most 1 header name or column number may be specified.

Default value: {} (no column specified)
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.

Use the year 1900 (lies well in the past and this is good so) in order to declare bank holidays as anually repeating bank holidays. Example: "1900-07-14" declares French national holiday for every year.

Default value: {} (Empty set)
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.

Use the year 1900 (lies well in the past and this is good so) in order to declare school holidays (vacation) as anually repeating events.
Example: "1900-07-01", "1900-07-21" declares the first 3 weeks of every July as vacation.

Default value: {} (Empty set)

Return value

TypeDescription
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.
falseAt least in one table row, the total value cannot be distributed into the tiven output range. Either the earlier sums / later sums have not been provided, or all days are blocked (e.g. time range consisting just of weekends, bank holidays or school holidays (vacation)), or end dates lie before start dates.

Exceptions

Batch size is negative, end date is earlier than start date, invalid combinations of intervals and usances.

Examples

       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[] );

Output

    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
Try it yourself: Open LIB_Function_table_distribute.b4p in B4P_Examples.zip. Decompress before use.

See also

distribute