Inserting new records to complete a time line

Question

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.

Example Transaction Query 2021.10.29.xlsx

NicoleJohnson · Accepted Answer

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

NicoleJohnson · Accepted Answer

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