Overview
During data processing, you may want to calculate the time interval between the dates of the previous row and the dates of the current row. You can view the intervals between the dates of orders and conduct further data analysis.
You can download the sample data Order Sales Detail Table.xlsx.
Procedure
Date Sorting
Upload the sample data and sort the dates, as shown in the following figure.
Calculating the Date Rank
Since multiple orders exist in a single day, you need to add a column 1, as shown in the following figure.
Calculate the cumulative value of column 1 and obtain different ranks for each row, as shown in the following figure.
Calculating the Date in the Previous Row of Each Signing Date
First, convert order dates to numerical formats before using the EARLIER function to zigzag the dates, as shown in the following figure.
Calculate Value of the Previous Row based on the rank of Order Date-Time and use the EARLIER function to zigzag the dates, as shown in the following figure.
Value of the Previous Row can be obtained by DEF(SUM_AGG(Order Date-Time),Rank,Rank+1=EARLIER(Rank)).
Convert the timestamps of the dates of previous rows into data in the date format. Since no previous row exists for the first order date, you need to use the IF function to determine if values of the previous rows are empty and convert non-empty values, as shown in the following figure.
Date of the Previous Row can be obtained by IF(Value of the Previous Row=null,null,TODATE(Value of the Previous Row))
Calculating the Time Interval
Use the DATEIF function to calculate the time interval between Date of the Previous Row and Order Date. Use the IF function to determine if values of the previous rows are empty, avoiding empty values being calculated, as shown in the following figure.
Time Interval Compared with The Previous Row can by obtained by IF(Value of the Previous Row=null,null,DATEDIF(Date of the Previous Row,Order Date,"d"))
Effect Display
You can group time intervals for order dates and calculate the percentage for each group in the component.
1. Copy the Time Interval Compared with the Previous Row field, click the drop-down icon of the field obtained, and select Convert to Dimension from the drop-down list.
2. Drag the dimension field obtained into the analysis area, click the drop-down icon, and select Interval Group Setting from the drop-down list, as shown in the following figure.
3. Drag the Count field into the Indicator bar, click the drop-down icon, and choose Quick Calculation > Percentage from the drop-down list. In this case, you can view the percentage of orders within different date intervals.
4. Select Pie Chart in Chart Type. In this case, you can view data details, as shown in the following figure.
