monthsend - script and chart function

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset containing a set of transactions for 2022 is loaded into a table called ‘Transactions’.

• A date field provided in the DateFormat system variable (MM/DD/YYYY) format.

• A preceding load statement containing:

  • The monthsend function which is set as the field, ‘bi_monthly_end’. This groups transactions into bi-monthly segments.

  • The timestamp function which returns the starting timestamp of the segment for each transaction.

Load script

SET DateFormat='MM/DD/YYYY'; 

Transactions: 
    Load 
    *, 
    monthsend(2,date) as bi_monthly_end, 
    timestamp(monthsend(2,date)) as bi_monthly_end_timestamp 
    ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,1/7/2022,17.17 
8189,1/19/2022,37.23 
8190,2/28/2022,88.27 
8191,2/5/2022,57.42 
8192,3/16/2022,53.80 
8193,4/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/22/2022,87.21 
8196,6/15/2022,95.93
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77 
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• id

• date

• bi_monthly_end

• bi_monthly_end_timestamp

The ‘bi_monthly_end’ field is created in the preceding load statement by using the monthsend() function. The first argument provided is 2, dividing the year into bi-monthly segments. The second argument identifies which field is being evaluated.

Transaction 8195 takes place on May 22. The monthsend() function initially divides the year into bi-monthly segments. Transaction 8195 falls into the segment between May 1 and June 30. As a result, the function returns the last millisecond of this segment, 06/30/2022 11:59:59 PM.

Overview

The same dataset and scenario as the first example are used.

In this example, the task is to create a field, ‘prev_bi_monthly_end’, that returns the first millisecond of the bi-monthly segment before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY'; 

Transactions: 
    Load 
    *, 
    monthsend(2,date,-1) as prev_bi_monthly_end, 
    timestamp(monthsend(2,date,-1)) as prev_bi_monthly_end_timestamp 
    ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,1/7/2022,17.17 
8189,1/19/2022,37.23 
8190,2/28/2022,88.27 
8191,2/5/2022,57.42 
8192,3/16/2022,53.80 
8193,4/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/22/2022,87.21 
8196,6/15/2022,95.93 
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77 
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• id

• date

• prev_bi_monthly_end

• prev_bi_monthly_end_timestamp

By using -1 as the period_no argument in the monthsend() function, after initially dividing a year into bi-monthly segments, the function returns the last millisecond of the previous bi-monthly segment to when a transaction takes place.

Transaction 8195 occurs in the segment between May and June. As a result, the previous bi-monthly segment was between March 1 and April 30 and so the function returns the last millisecond of this segment, 04/30/2022 11:59:59 PM.

Overview

The same dataset and scenario as the first example are used.

In this example, the organizational policy is for April to be the first month of the financial year.

Create a field, ‘bi_monthly_end’, that groups transactions into bi-monthly segments and returns the last millisecond timestamp of the segment for each transaction.

Load script

SET DateFormat='MM/DD/YYYY'; 
						
Transactions: 
    Load 
    *, 
    monthsend(2,date,0,4) as bi_monthly_end, 
    timestamp(monthsend(2,date,0,4)) as bi_monthly_end_timestamp 
    ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,1/7/2022,17.17 
8189,1/19/2022,37.23 
8190,2/28/2022,88.27 
8191,2/5/2022,57.42 
8192,3/16/2022,53.80 
8193,4/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/22/2022,87.21 
8196,6/15/2022,95.93 
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77 
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• id

• date

• bi_monthly_end

• bi_monthly_end_timestamp

By using 4 as the first_month_of_year argument in the monthsend() function, the function begins the year on April 1. It then divides the year into bi-monthly segments: Apr-May, Jun-Jul, Aug-Sep, Oct-Nov, Dec-Jan, Feb-Mar.

Transaction 8195 took place on May 22 and falls into the segment between April 1 and May 31. As a result, the function returns the last millisecond of this segment, 05/31/2022 11:59:59 PM.

Overview

The same dataset and scenario as the first example are used. However in this example, the dataset is unchanged, and loaded into the app.

In this example, the task is to create a calculation that groups transactions into bi-monthly segments and returns the last millisecond timestamp of the segment for each transaction as a measure in a chart object of an app.

Load script

SET DateFormat='MM/DD/YYYY'; 
						
Transactions: 
Load 
* 
Inline  
[ 
id,date,amount 
8188,2/19/2022,37.23 
8189,3/7/2022,17.17
8190,3/30/2022,88.27 
8191,4/5/2022,57.42 
8192,4/16/2022,53.80 
8193,5/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/22/2022,87.21 
8196,6/15/2022,95.93 
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77 
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

date

To fetch the last millisecond timestamp of the bi-monthly segment when the transaction took place, create the following measures:

• =monthsEnd(2,date)

• =timestamp(monthsend(2,date))

The ‘bi_monthly_end’ field is created as a measure in the chart object by using the monthsend() function. The first argument provided is 2, which divides the year into bi-monthly segments. The second argument identifies which field is being evaluated.

Transaction 8195 takes place on May 22. The monthsend() function initially divides the year into bi-monthly segments. Transaction 8195 falls into the segment between May 1 and June 30. As a result, the function returns the first millisecond of this segment, 06/30/2022 11:59:59 PM.

Overview

Open the data load editor and add the load script below to a new tab.

In this example, a dataset is loaded into a table called ‘Employee_Expenses’. The table contains the following fields:

• Employee IDs

• Employee names

• The average daily expense claims of each employee.

The end user would like a chart that displays, by employee id and employee name, the estimated expense claim for the remainder of a period of their own choosing. The financial year begins in January.

Load script

SET vPeriod = 1; 

Employee_Expenses: 
Load 
* 
Inline  
[ 
employee_id,employee_name,avg_daily_claim 
182,Mark, $15 
183,Deryck, $12.5  
184,Dexter, $12.5 
185,Sydney,$27 
186,Agatha,$18 
]; 

Results

Load the data and open a new sheet.

At the start of the load script, a variable, vPeriod, is created that is tied to the variable input control.

Do the following:

1. In the assets panel, click Custom objects.

2. Select Qlik Dashboard bundle, create a Variable input object.

3. Enter a title for the chart object.

4. Under Variable, select vPeriod as the name and set the object to show as a Drop down.

5. Under Values, click Dynamic values. Enter the following:

   ='1~month|2~bi-month|3~quarter|4~tertial|6~half-year'.

Create a new table and these fields as dimensions:

• employee_id

• employee_name

To calculate the accumulated interest, create this measure:

=floor(monthsend($(vPeriod),today(1))-today(1))*avg_daily_claim 

Set the measure’s Number formatting to Money.

The monthsend() function uses the user input as its first argument and today’s date as its second argument. This returns the end date for the user selected period of time. Then, the expression returns the number of days that remain the selected period of time by subtracting today’s date from this end date.

This value is then multiplied by the average daily expense claim by each employee to calculate the estimated value of claims each employee is expected to make in the remaining days of this period.