Question
Calculating the difference using lookup function ignoring null values:
For example,
Last Modified Date: 03 Jul 2023
Environment
- Tableau Desktop
- Windows 10
- Excel
Answer
Using PREVIOUS_VALUE function to resolve it.1. Create a calculated field
name: TS_NULL0
value: IFNULL(SUM([Value]),0)
2. Create a calculated field
name: TS_presentvalue
value:
IF ZN(([TS_NULL0]))=0 THEN PREVIOUS_VALUE([TS_NULL0] )
ELSE [TS_NULL0] END
3. Create a calculated field
name: TS_difference
value: [TS_presentvalue]-LOOKUP([TS_presentvalue],-1)
4. Drag [TS_difference] into the Measure Values card.
See the attached workbook.
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials