Give a chestnut! Tableau Tips (109): Use LOD to Calculate Product Sales Cycle

About chestnuts

This issue of Lizi is the second submission of @棒棒的东 (I'm just a porter of knowledge), thank him for sharing.

@棒棒的东 In actual business, he encountered the need for analysis: to calculate the time difference between a single product at each sales node and the initial sales date. After communicating with me and combining his own exploration, he finally achieved the desired visualization effect (as shown below).

Considering that this business requirement has reference significance for the data fans who study business flow, he hopes to share his method with everyone.

In this issue of "Give a Chestnut", the Tableau technique that Ada wants to share with you is: use LOD to calculate the product sales cycle.

To facilitate learning, Lizi uses Tableau's own supermarket data source. If you can’t find the data fan with its own data source, you can contact Xiaoyou dkmxiaoyou on WeChat~

In addition, Lizi used LOD-Level of Detail Expression ☜ Click to learn more.

Specific steps are as follows:

1. Connect to the data source

2. Set up a layered structure

Set the "category level" hierarchical structure, the order of inclusion is: category-subcategory-product name.

3. Create a calculated field

Create Sales Days calculation field:

DATEDIFF("day",{FIXED [product name]:MIN([order date])},[order date])+1

Tips: The number of sales days means "from the second order date, the difference between each order date node and the first order date (minimum date)", which will be displayed from the first day.

4. Create a chart: sales days

Drag the order date to the column, drag the sales to the row, adjust the granularity of the order time to days, and adjust the worksheet format.

Drag the subcategory and product name to the filter respectively, and set the display filter. Chestnuts are examples by sub-category (appliance) and product name (Cuisinart blender, white), as shown in the figure below.

5. Set up dual axis

In order to ensure that the worksheet is beautiful and the sales days label is displayed only once, the view is dual-axis processed here.

Copy the "Sales" capsule in the row and drag it to the right of the original "Sales" capsule. The first axis view is displayed as a "line", and the second axis view is displayed as a "circle". Right-click the dual axis in the "Sales" field on the right, synchronize the axis and hide the title, and adjust the view format.

6. Display label

Switch to the second view and drag the "Sale days" calculation field to the label. Modify the label display, click the red circled in the following figure, and modify it to "the <total (sales days)> day".

After the completion, you will find that the presentation effect of the chart is more intuitive.

Chestnut supplement

In actual business, there may be a situation where a single value corresponds to the total number of days sold for multiple product names (not reflected in the chestnut, but it should be often encountered in actual business). Here we only need to do one step: analysis-cancel aggregation measure. In this way, the values corresponding to multiple product names will be displayed scattered!

Have you gotten the Tableau skills in this issue? Give it a try!