Off the top of my head the only way I can think to do this without parameters is by creating a custom sql data connection that looks up the values before the data hits Tableau.
I think paramaters would be simpler. You could give it a range from the start of the dataset to a date way in the future to avoid having to adjust the parameter settings again later on.
I have attached a workbook I have just rather hastily put together that I think shows the data int the format you require.
If you would like me to run through how I dont mind doing that.
All the best,
You can do this without parameters using lookup() as part of the filter (and as the way to find the prior values). By using lookup([date],0) in the filter the whole dataset is available for the lookup and the filter is only after this.
EDIT: This is pretty much what Darren did as well for finding the prior values - I like the fact we both had a 'test' table at the beginning.
Note the calculations I have used depend on you having a value for every date. If that is not the case then we would need to be checking the dates.
prior dates.twbx.zip 1.1 MB
I would use Darren's method of parameters that push to the future but with some date checking for missing dates that also removes the need for table calcs.
Cheers Alex - this is useful stuff!
I guess you could also use some logic so that if the parameter is greater than the max date in the data, default to the max. Then, even though the selector could be pushed far right, the view would always be valid.
That sounds great!! It is exactly what I need! I will try it and let you know whether it works for me!!
Thanks a lot!
For some reason I don't seem to get it working with the parameter. Do I have to do anything special? I have created the parameter, set up my calculated fields and the true\false date clause. Am I missing something?
I worked it out!! Thanks a lot!! Alex and Darren - very useful!
Thanks Alex for the simple walkthrough. I am able to duplicate the functionality but am stuck on how to change the filter calculation from range of dates to a single select slider. Ideas?
Thanks in advance.
I've been working around all these stuff (today - yesterday values). I've found all these solutions very usefull, and' I've done another one way to do this using parameters (sorry if it is already listed).
One anoying thing about all these is that you have to create a "yesterday's value" for each metric, so I've made a way to avoid this anoying thing.
The way I do it is to create another "Order Date" dimension that has only values for "today" parameter, "yesterday" and "seven days ago", and "null" for the rest of the values. If I list this dimension and I filter the NULL values, it has only the desired values.
See the file attached (modified from the usefull Alex Kerin's "prior dates")
What do you thing about this solution? Do I miss something?
Thanks a lot!!!
prior dates.twbx 1.2 MB
As you mentioned you were able to work it out on the case below as mentioned by Alex. Could you please share how u were able to get view when date parameter is greater than max date in the data, default to the max.
Alex: "I guess you could also use some logic so that if the parameter is greater than the max date in the data, default to the max. Then, even though the selector could be pushed far right, the view would always be valid."