This depends on what you want to do with the results. Another approach, without using parameters, will be to use a range of date filter which defaults to min and max dates in your dataset. Make sure the full date range is selected before publishing to the server.
Date filter is used to filter the data to the last 12 months. Modify this if you want to sum over a different date range.
Row filter is used to keep only the last row per Region. This is because [Date] is in the view and [Total Sales] is a running sum.
LAST() == 0 will work in most cases. However, if you have different maximum dates per region, then it is necessary to use a more robust formula as defined above.
Row Filter, Date Filter and Total Sales are all table calcs. Set each of them up to compute as shown below.
Making the date slider dynamic is a bit fiddly. Different setups will work, however you can follow the steps in Tableau's knowledge base.
Use Non-null values in the filter. When this special value is selected, then the range of dates or range of values filtered will bring in new dates or values when the underlying data is updated
- Right-click the filter on the Filters shelf and select Edit Filter…
- In the Filter dialog, click the Special tab
- For Special, select Non-null dates and click OK
- Open the filter options from filter in the view (click drop down)
- Select 'Only Relevant Values'
- Reopen options and select Customize > Show Null Controls
- Select 'Non-Null Values Only' from new option on filter
Note: The end values of the range will only update if the drop down says "Non-Null Values Only". Once the range of the filter has been changed, the drop down will default to "Values in Range" and it will be necessary to change the drop down back to "Non-Null Values Only" before saving the workbook. Steps 6-7 allow a way in the interface for the user to quickly change the filter back to "Non-Null Values Only"
Alternatively, you can set the option to All dates rather than Non-null dates. I often use this method. However, note that this method is sensitive to the timing of the filter setup. For this to work, set the date filter to 'All dates' only when you are ready to publish the work. Do not touch the date slider after that.
Hope this helps.
Thanks! I think I am getting close but just fixing some errors and making sure I have mine set up the same. I have a lot of calculations that compare the default date range to the current date (for example, revenue last 12 months)
For example the below:
The date range is the default date, then i created a date range (copy) that I am filtering by using the select date calc. But when doing this it is not giving the correct numbers (they are always higher)
Before, I had the same formula, instead of date range (copy) I just had today(), and since there is no future date it didn't add in anything else. Can you please advise if this is correct?
Thank you. I am counting number of clients at a certain point in time. It calculated revenue for the past 12 months (as of the anchored date), to the revenue 24 months ago (from anchored date). Would this work in that regard? I don't want to select a range of dates, just one date that the rest of the dashboard would filter to, and treat that date the same as it currently is treating today()
Please provide your packaged workbook (twbx) with data extracted in it. If you have any confidential data, then please provide with some dummy data. It is difficult to see what's going on otherwise.
Please see attached. I created some dummy data to go off of with just a few rows. When I use the actual data, it takes extremely long to load and select the date parameters when I add the parameters, and it's not giving accurate information.
The 2 red tabs are what I was using to test the file, one being a dashboard and one being one of the tables.
Run As Date v3.twbx 247.0 KB
There are too many calculations here. Is there any particular one you need me to check which is giving you an incorrect output?
Also, I see that you are using FIXED LODs. So, you should add any filter you are using to context by right-clicking on them.
Sure, sorry for the number of calcs. We can just go the CNTS (LTM) (3) Tab which is colored red. The CNT(Beg Clients (LOD)) calc is giving me inflated numbers when I manipulate the date
The main issue you are having is that the selected date is not changing even though you are changing the parameter values. And that's because the month parameter has not been set as required here.
The month parameter value expected per the above code is a 2-digit number in string format, And so, once I changed it, the selected date value is changing and you should have what you need. Also, if the same client exists across different BU's, then you should include the BU also in the fixed LODs. So, please check that.
Hope this helps. Updated workbook is attached.
Run As Date v3.twbx 326.0 KB