I have a data set that is based on transactional activity that is published to Tableau Service. Not all items are transacted daily leaving gaps in the date sequence, so I would like to insert a row to represent the missing day(s) and carry down the value from the previous recorded transaction so when the data is published I can have a consistent range of dates for trending.
Solved! Go to Solution.
This is a perfect use case for Multi-Row tool (to find the next date in the list) and the Generate Rows tool (to generate a record for each day in between the current record's date and the next one). You might want to sort your dates first, before the Multi-Row tool, and look out for Group By other fields if you need to account for that in your real source data. See attached for example!
Cheers!
NJ
Now I am realizing I want to extend the timeline to the current even if the last transaction has not posted yet. For example the last transaction was posted on 10/31/21 so I would like to have the timeline continue to today with the same value until a new transaction is posted. Can you use the same tool?
Absolutely! You'll just need to modify the formula you use for the "NextDate" field in the Multi-Row Formula tool... original version shows a Null value for the last row, since there isn't a next row to look at. Instead, if you use an IF statement to tell you when you have reached the last row, you can use DateTimeToday() to tell it to stop when it reaches today.
If IsEmpty([Row+1:Transaction Date])
Then DateTimeToday()
Else [Row+1:Transaction Date]
Endif
Give that a try!
Cheers,
NJ