Fills any missing or null values in the specified column with the most recent non-blank value, as determined by the specified sort order and optional grouping.

window value:FILL(myNumber) order:'Date'

Output: Generates a new column, which contains all values from the myNumber column with any null cells filled by the most recent non-blank value, as determined by the Date column.

window value:FILL(col_ref, [k_integer]) order: order_col [group: group_col]

ArgumentRequired?Data TypeDescription
col_refYstringName of column whose values are applied to the function
k_integerNinteger (positive)

NOTE: Unused by this function.

For more information on the order and group parameters, see Window Transform.

col_ref

Name of the column whose values are filled when null. 

Required?Data TypeExample Value
YesString (column reference)myColumn

k_integer

NOTE: While accepted by the function, this parameter is not used by the function. If specified, it must be a positive integer.

Required?Data TypeExample Value
No. Unused.Integer1

Example - Fill with quarterly forecast values

Your monthly sales data includes amount sold for each month. However, the source system only provides the quarterly forecast as part of the data for the first month of each quarter. You can use the FILL function to add the prior forecast to each month's data. 

Source:

DateAmountForecast_Qtr
1/31/155231400
2/28/15135 
3/31/15824 
4/30/153051500
5/31/15763 
6/30/15421 
7/31/156061600
8/31/15477 
9/30/15785 
10/31/154431700
11/30/15622 
12/31/15518 

Transform:

You can use the following transform to fill the prior forecast value for each blank month in the Forecast_Qtr column. Note that the order parameter must be set to Date to establish the proper sorting:

 

window value: FILL(Forecast_Qtr) order: Date

You can now drop the Forecast_Qtr column and rename the generated window column to the dropped name.

To see how you are progressing each month, you might use the following transform, which computes the average forecast for each month:

derive type:single value:NUMFORMAT((Forecast_Qtr/3),'####.##') as:'Forecast_Month_Avg'

You can then compare this value to the actual Amount value for each month:

derive type:single value:NUMFORMAT(((Amount - Forecast_Month_Avg)/Forecast_Month_Avg)*100, '##.00') as:'MonthActualVForecast_Pct'

Results:

DateAmountForecast_QtrForecast_Month_AvgMonthActualVForecast_Pct
1/31/155231400466.6712.07
2/28/151351400466.67-71.07
3/31/158241400466.6776.57
4/30/153051500500-39.00
5/31/15763150050052.60
6/30/154211500500-15.80
7/31/156061600533.3313.63
8/31/154771600533.33-10.56
9/30/157851600533.3347.19
10/31/154431700566.67-21.82
11/30/156221700566.679.76
12/31/155181700566.67-8.59