Suppose you have Sales data with Customer Name, Order Date, and Sales columns. One row represents one order. Now you want to know the date range between the oldest purchase date and the latest purchase date per Customer Name. There are 2 ways to do this.
1. If you want to keep all the rows.
If you want to keep all the rows and you want to add a new column for the date ranges, here is how.
Choose “Group By” from the column header menu of the “Customer Name” column.
Choose “Create Calculation” from the column header menu of the “Order Date” column.
Set “Range” for the new column name and type in the following expression in the Calculation Editor.
max(`Order Date`) - min(`Order Date`)
then it will create a new column “Range” with the date range between the oldest purchase date and the latest purchase date per Customer Name.
The data type of the “Range” column is “difftime”. If the “Order Date” column is a Date column, the unit is usually “Days”. You can explicitly specify the unit by changing the data type to Numeric from the column header menu of the “Range” column.
2. If you don’t need to keep all the rows.
If you just need date ranges for customers and you don’t need to keep all the rows, here is how.
Choose “Summarize” from the column header menu of the “Customer Name” column.
Set “Customer Name” for Group By and choose “Custom” at Value. Type in the following expression in the Expression and click “Run”.
max(`Order Date`) - min(`Order Date`)
Now, you have a list of customers and the date range between the oldest purchase date and the latest purchase date. The data type of the “Range” column is “difftime”. You can explicitly specify the unit by changing the data type to Numeric from the column header menu of the “Range” column as shown above.
