Overview
Background
You may need to calculate the data for a certain period, such as the sales of the previous three months.
Calculating the data for a certain period on a day-by-day or month-by-month basis is very helpful for timeliness analysis, seasonal analysis, and performance evaluation. This calculation method allows you to better understand business dynamics, supporting decision-making and strategic planning.
Preview
This document introduces how to calculate the sales of the previous three months on a month-by-month basis. As shown in the following figure, the targeted value corresponding to 2013-03 is the total sales from 2013-01 to 2013-03; the targeted value corresponding to 2013-07 is the total sales from 2013-05 to 2013-07.
Implementation Method
Use the DEF and EARLIER functions.
Procedures
Adding Data
Sample data: Supermarket Sales Data.xlsx
Create a subject and upload the sample data.
Processing Data
1. To sum up the monthly sales, click Group Summary and group sales by month to reduce data volume and facilitate calculation.
2. Since the EARLIER function is used for inter-row calculation in subsequent steps, adding a number column helps determine which is the previous or next row.
Click Summary Column and rank Order Date in ascending order.
3. Click Save and Update.
Creating a Component
1. Select Group Table as the chart type. Drag Order Date (grouping method: Year-Month) into Dimension and drag Sales (value format: Thousand) into Indicator.
For easier understanding, you can copy the Number field, convert it to Dimension Number1, and drag Number1 (grouping method: Group Records with Same Value) into Dimension.
2. Calculate the sales of the previous three months. Add a calculation indicator named Sales of the Previous Three Months and enter the formula shown in the following figure.
| Formula | Explanation | Reference |
|---|---|---|
SUM_AGG(Sales) | Sums up the total sales amount. | |
DEF_ADD(SUM_AGG(Sales), []) | Groups data by the current Dimension and sums up the sales of each group. | |
DEF_ADD(SUM_AGG(Sales),[],[Number<=EARLIER(),Number>=EARLIER(Number-2)]) | Filters out the numbers corresponding to the previous three months. Sums up the sales of the previous three months. |
3. Drag Sales of the Previous Three Months into Indicator and change the Value Format to Thousand.
Demonstration
See section "Preview."
