KNOWLEDGE BASE

Display Beginning Or Ending Month After Data Of These Month Are Filtered Out


Published: 13 Dec 2019
Last Modified Date: 04 Feb 2020

Question

Upon applying filters, the beginning or ending month was removed from the view because data of these months are filtered out.  For example, without any filters, years and months of Order Date are displayed from January 2016 to December 2019.
User-added image
However, after applying Category, Sub-Category and Product Name filters, years and months of Order Date are displayed from April 2016 to September 2019 only even though Show Missing Values are enabled because Sales data is filtered out at the beginning or ending month. How can I display all months exist in the data source even after filters are applied?
User-added image
 

Environment

  • Tableau Desktop

Answer

The following steps are demonstrated with Sample - Superstore.
  1. Create date master.xlsx which includes the following sheets:
    • Sheet1: all years exist in Order Date of Sample - Superstore.
    • Sheet2: all months exist Order Date of Sample - Superstore.
  2. Open date master.xlsx by Tableau Desktop.
  3. Click Add to the connection pain and open Sample - Superstore.
  4. Place Sheet1 from the date master to the date source pane.
  5. Place Orders from the Sample - Superstore to the data source pane.
  6. Select Full Outer join and click Edit Join Calculation at the left side of the "=".
  7. Enter 1 and click OK.
  8. Click Edit Join Calculation at the right side of the "=".
  9. Enter 0 and click OK.
    As a result of this step, all rows in the Sheet1 and the Orders are included to the data source. Null is displayed to columns which does not match the other table.
  10. Place Sheet2 from the data master to the data source pane.
  11. Select Inner Join click Edit Join Calculation at the left side of the "=".
  12. Enter 1 and click OK.
  13. Click Edit Join Calculation at the right side of the "=".
  14. Enter 1 and click OK.
    As a result of this step, all rows in the Sheet1 are joined to each row in the Sheet2.
  15. Open a new sheet and click Analysis > Create Calculated Field.
  16. Enter the following formula named as Order Date (From date master).
    IFNULL([Order Date],MAKEDATE([year],[month],1))
  17. Place Order Date (From date master) to Columns with right-clicking and select MY(Order Date (From date master) ).
  18. Place Sales to Rows.
  19. Place Category to Filters, then select Null and Furniture.
  20. Place Sub-Category to Filters, then select Null and Chairs.
  21. Place Product Name to Filters, then select Null, Bevis Steel Folding Chairs and Global Airflow Leather Mesh Back Chair, Black.
  22. Place Segment to Color.

Additional Information

To achieve the desired result, selecting Null for all filters are required.
Did this article resolve the issue?