Sales and gross profit margin analysis

In this chapter, it is necessary to analyze the trend of sales and gross profit, and obtain their Month-on-month chain ratio, so as to grasp the company's sales profit. As shown below:

2.1 Create the component

Use pre-defined Excel dataset "Ordersdetail" to create a component.

Ordersdetail.xlsx

(Excel is generated from the SQL below, and the data is from FRDemo)

select ORDERS.ORDERID, SIGNDATE, AMOUNT as TOTALSALES, sum(ORDERSDETAIL.QUANTITY*COST)as TOTALCOST, (AMOUNT-sum(ORDERSDETAIL.QUANTITY*COST)) as GROSSPROFIT from 
ORDERS join ORDERSDETAIL on ORDERS.ORDERID=ORDERSDETAIL.ORDERID
join products on ORDERSDETAIL.PRODUCTID=products.PRODUCTID
group by ORDERS.ORDERID, SIGNDATE, AMOUNT

2.2 Design custom chart

Select "Custom Chart" as the chart type, then drag the "SignDate" field and the "TotalSales" field into the corresponding analysis area on the right, and drag the "TotalSales" field twice. As shown below:

Click the drop-down in the "SignDate" field and select the "Year Month" grouping to display the sales in a monthly form, as shown in the figure below:

2.3 Sales month-on-month growth

In the second "TotalSales" field drop down, select "Quick Calculation > Chain growth rate", as shown in the figure below:

Click the second "TotalSales" field drop-down, "Set Display Name", enter "Sales MOM Growth", as shown in the figure below:

2.4 Change display format

Under "Graphic Properties", find "TotalSales (Sum)", as shown in the following figure:

Display the indicator of totalsales (sum) as a "line" graph, change the display form of the indicator, and check "View all data", as shown in the following figure:

Note: In order to be smooth when editing components, all data is not displayed by default (only part of the data is used for calculation), so the results displayed on the editing interface are not accurate. If you want to see all the data, check to see all the data.

2.5 Set value axis

"TOTALSALES" and "Sales MOM Growth" share the left value axis, but the value of the chain ratio is very small, and it is difficult to display the value of the chain ratio by sharing the same axis. Therefore set the dual value axis.

Select the drop-down of the "Sales MOM Growth" field, "Set Value Axis", as shown in the figure below:

Select "Right Value Axis" and click "OK", as shown in the figure below:

2.6 Set colors

2.6.1 Set the color for sales MOM growth

Copy a "Sales MOM Growth" field and drag it into the color bar under "graphic properties> Sales MOM Growth", as shown in the figure below:

Drag the field into the color bar, you can set the color according to the field value as the basis for conditions.

Click on the color setting to set the color for the "Sales MOM Growth". Columns with a MOM growth greater than 0 are green, and columns with a MOM growth less than 0 are red.

Click on the color bar, check "Area Gradient>Custom", set the number of intervals to 2, and set the interval and corresponding colors in detail below. As shown below:

2.6.2 Set the color for totalsales

Since no condition setting is required, no fields need to be dragged into the color bar. Click on the color setting and select yellow, as shown in the figure below:

2.7 Set numeric format

In the Totalsales field, drop down and select "Numeric Format", as shown in the figure below:

Set the value unit to "Thousand", as shown in the figure below:

Follow the same method to set the numerical format of "Sales MOM Growth", and set the numerical format to percentage, as shown in the figure below:

2.8 Set the label

In order to easily see the detailed values of the columns and line graphs on the chart, labels need to be set.

Drag the "TOTALSALES" field from the left into the label column under "Chart Properties > TOTALSALES", as shown in the figure below:

If you also need to look at the "Sales MOM Growth" label, copy a "Sales MOM Growth" on the vertical axis and drag it into the label column under "Chart Properties> Sales MOM Growth", as shown in the following figure:

2.9 Set the alert line

In order to make the chart more perfect and more intuitive, we can set the alert line of the sales average.

Select "Dropdown > Set the analysis line > Cordon (Landscape)", as shown in the figure below:

2) Enter the detailed setting page, "Add a cordon", rename it to "Sales monthly AVG", add a formula to find the average of sales, as shown in the figure below:

2.10 Preview

Sales fluctuate and show a downward trend.

3.1 Procedure

To add components, still use the "Ordersdetail" dataset, as shown in the following figure:

Select the custom chart and drag fields into it, as shown in the figure below:

For the subsequent steps, please refer to Chapter 2 of this article.

3.2 Preview

4.1 Procedure

To add components, still use the "Ordersdetail" dataset, as shown in the following figure:

Since there is no "gross profit margin" field in the original table, users can get it by creating a new calculation field. As shown below:

An aggregate function SUM_AGG is used here. The aggregate function is used when summarizing first, and then calculating.

First sum up "gross profit" and "totalsales", and then divide them to obtain "gross profit margin". When "Date" is switched to "Year and Month", SUM_AGG (Gross Profit) returns the total sum summarized by month. When it is switched to "Year, Month and Day", SUM_AGG (Gross Profit) returns the sum summarized by date.

Drag into the field, as shown in the figure below:

For the subsequent steps, please refer to Chapter 2 of this article.

Modify the component name. Named "monthly gross profit margin && chain trend analysis". In the same way, the previous components were named "monthly sales && chain trend analysis" and "monthly gross profit && chain trend analysis". Results as shown below:

4.2 Preview

After unchecking the legend under "Component Style", click "Enter the dashboard" to adjust the position of the component, as shown in the following figure: