Judge sales based on a particular day

1.1 Expected effect

See how daily sales compare to a fixed date, using a particular day's sales as a benchmark. For example, the user wants to request the daily sales compared to the sales at the beginning of the month. The following chart shows this.

The scenario also applies to taking the sales of a category as a benchmark, judging the sales of other categories based on the ratio of other categories to a fixed one.

1.2 Realization idea

Find the sales and ratios at the beginning of the month from the self-service dataset.

2. Example

Using Demo Data "Store sales statistics"

2.1 Calculate the ratio

2.1.1 Selected Fields

Use "Store sales statistics" to create a self-service dataset, check "Date" and "Sales", as shown below.

2.1.2 Group Summary

1）Create a new group summary, drag in two "Date" fields in the group column, and drag in "Sales" in the summary column, as shown in the following figure.

2）Rename "Date 1" to "Year-Month", and select "Year Month" for the date group, as shown below.

2.1.3 New column "Based on the beginning of the month"

Add a new column and enter the formula (see DAY for details of the DAY function). As shown in the figure below.

In the "Based on the beginning of the month" column, sales data are available on the 1st of each month, and the rest are 0. As shown in the figure below.

2.1.4 New column "Sales at the beginning of the month"

The steps are shown in the figure below.

Click OK and you can see that the " Sales at the beginning of the month" field is populated with data from the beginning of the month, as shown in the following figure.

2.1.5 New column "Ratio"

Calculate the value of "Sales/Sales at the beginning of the month", as shown below.

2.2 Effect view

The value of "Sales/Sales at the beginning of the month" is shown in the following graph.