Calculate the cumulative value from the specified time

1. Overview

1.1 Expected effect

For example, using 2014-01-01 as the starting time, calculate the "cumulative contract amount" after that date, as shown in the following figure.

1.2 Implementation idea

Use the DATESUBDATE function to calculate the time difference between the existing data and 2014-01-01.

Use the IF function to assign a value of 0 if the time difference is earlier than 2014-01-01, i.e., the time difference is less than 0. Otherwise, output the "contract total amount" data.

After sorting the contract time in ascending order, use the new cumulative value/group to calculate the cumulative contract amount.

Example data: "Industry Data>Estate Indusry>FACT_SALE_CONTRACT"

2.1 Create a Self-Service dataset

Select all fields under "FACT_SALE_CONTRACT" as shown in the following figure.

2.2 Assign a value of 0 to all data earlier than the specified time

Add "New column", name it, and enter the formula: DATESUBDATE(Signing Time, "2014-01-01", "d/D"), calculate the time difference between existing data and 2014-01-01, click "OK", as shown in the figure below:

Add "New column", name it and enter the formula: IF(time difference <0,0, contract total amount), if the time is earlier than 2014-01-01 that is, the time difference is less than 0, then assign the value to 0, otherwise output "contract total amount" data, click "OK", as shown in the figure below.

2.3 Calculate the cumulative contract amount after the specified time

The "signing time" is arranged in "ascending order", as shown below.

Add "New column", name it and calculate cumulative contract amount, and click "OK", as shown in the following figure.

2.4 Effect view

See Section 1.1 of this document for details.