KNOWLEDGE BASE

How to show missing date values from the data set in the view and copy the last available date value for the missing values


Published: 20 Jan 2020
Last Modified Date: 20 Jan 2020

Question

How to show missing date values from the data set in the view and copy the last available date value for the missing values    

Environment

  • Tableau Desktop

Answer

1) Connect to your data source and build a view with Date field in rows and the Value field in text like below:
User-added image

2) To show the missing dates in between, Right-Click the Date field and  click Show missing values
User-added image


3) To copy the last available date value to all the succeeding missing values, please follow the below steps:
   a) Create a calculated field called Zero value if null with the following formula to replace null values for missing dates with 0 : IFNULL(SUM([Value]),0)
    User-added image
   b) Create another calculated field called New value with the following formula: 
        if ZN(([Zero value if null]))=0 then PREVIOUS_VALUE([Zero value if null] )
        ELSE
            [Zero value if null]
        end

4) Use the New value in the view. The Final result will be like below:
User-added image
 
Did this article resolve the issue?