This article explains how to perform the Tableau Date Calculation using a parameter with an example. In general, when you want to show the sales trend, by default, it shows the sales by year or month, but not both. However, use the parameter and the DATETRUNC function combination. You can allow the users to choose the sales trend by year, quarter, month, day, hour, etc.
To demonstrate the Tableau Date Calculation using parameter values, we used the Adventure Works DW database. Please visit the SQL Source and SQL Tutorial article to understand the Data Source.
Tableau Date Calculation using Parameter
Drag and drop the Order Date to the Columns Shelf and Sales Amount to the Rows Shelf will automatically create a Line chart. Next, add profit to the Color Shelf and change the color per the requirement. We will use this Tableau line chart to explain the Date Calculation using a parameter. For the remaining charts in Tableau, please click here.
By default, Tableau will pick the Years from the Order Date and display the Discrete data. So, we must change it to the Continuous rows using the down arrow.
To fulfill this Tableau Calculation requirement, we need a parameter to select the date part (year, month, day, etc) and a calculated field to write the expression with parameter values. So, right-click the empty space above the Parameters section and choose the Create Parameter option from the context menu.
It will open the following Create Parameter window. Please change the name to Choose Date Part, the Data type to String, and set the Allowable values to List. Let me add the Date parts such as year, quarter, month, week, and day as the list values. Remember, these are not random words but the prescribed data parts for the DATETRUNC function. In this example, we want the users to control this part of the DATETRUNC function.
To utilize this string parameter in the report, we need a calculated field. So, click the down arrow beside the Order Date and select the Create Calculated Field.. option to write the below expression.
The following DATETRUNC expression creates a new date field that will return the year, Month, or day of the Order date based on the user selection.
DATETRUNC([Choose Date Part], [Order Date])
Please replace the Order date in the Columns shelf with the newly created Calculated Field. Next, click the down arrow and choose the Exact date. The next step is to click the down arrow beside the Choose Date Part parameter and select the Show Parameter option to see them.
Let me select the year as the Date Calculation parameter value, and you can notice that the Tableau Line chart shows the Sales by Year.
The line chart shows the monthly sales trend if you choose the month.
The you choose. Day, it shows day-wise sales trends.
