KNOWLEDGE BASE

How to Filter Data by 1st Half / 2nd Half According to Current Date Dynamically.


Published: 08 Feb 2022
Last Modified Date: 09 Feb 2022

Question

How to dynamically display data for the first and second half of the year.
Example:
Assumptions: 1st half : January - June 
                       2nd half : July - December.
If current date is 02/08/2022 , data will be displayed in the 1st half of the year.

Environment

  • Tableau Desktop

Answer

The procedure is as follows.

1. Connect to Sample Superstore.
2. Right-click and drag and drop [Order Date] to Columns, and select MONTH(Order Date).
3. Drag and drop [Sales] to Rows.
4. To calculate the current month, create a new calculated field with the following settings.
     Name : Current Month
     Calculation : MONTH(TODAY())
5. To determine whether the current month is the 1st or 2nd half of the year, create a new calculation field with the following settings.
     Name : 1st or 2nd half
     Calculation : IF [Current Month] >= 1 AND [Current Month] <= 6
                          THEN '1st'
                          ELSE '2nd'
                          END
6. To determine whether the month of [MONTH(Order Date)] is the 1st or 2nd half of the year, create a new calculated field with the following settings.
     Name : Order Date_1st or 2nd half
     Calculation : IF INT(MONTH([Order Date])) >= 1 AND INT(MONTH([Order Date])) <= 6
                          THEN '1st'
                          ELSE '2nd'
                          END
7. To show only the 1st or 2nd half , create a new calculated field with the following settings to create a filter.
     Name : Filter 1st or 2nd half
     Calculation : IF [1st or 2nd half] = [Order Date_1st or 2nd half]
                         THEN TRUE
                         ELSE FALSE
                         END
8. Drag and drop [Filter 1st or 2nd half] to Filter pane , and select 'TRUE'.
Did this article resolve the issue?