Have you tried using a Data Source filter? This will limit the amount of data that is pulled from the database and will never exceed 36 months.
Below is an example using Superstore sample data:
Also, yes it is possible to create a calculated field and limit the data based on the date selection.
IF DATEDIFF('month',[Yourdate],[ParameterDate]) < 37 AND
DATEDIFF('month',[Yourdate],[ParameterDate]) > 0
All you need to do is convert your integer month field to a date field and then you can use it in an above field.
Please mark helpful and/or correct if it helped.
Thank you for your response, insight, and input!
Data source filter is a great idea, however, still want to give the option to show full 60 months of data. The business wants to cut down initial load time while providing a full view on the trends.
To your second solution, converting to a date returns some funky numbers for me. This would have to be a change that is made in our database level to change the data type. Also, this solution requires a parameter which wouldn't be dynamic and would require monthly manual updates.
I've been trying to create calculations to evaluate the archive month id and then return a boolean value if greater than the max archive month offset by 37, but have been unsuccessful. Dynamic Parameters would really come in handy in this situation as we are looking for a streamlined automated updated process in terms of what months we show.
LOOKUP([Archive Month ID - MAX],LAST()-37)
IF [Archive Month ID]>[Archive Month Lookup]
Your recommendations are correct for the specific purposes they are intended to do....just running into some data issues. I would still need to test if turning archive month to a date in the database solves the issue of the query scanning all archive months. My thinking is that it would since it would need to calculate the datediff on the values...I am running into thing using Last() and Max().
So you are saying that:
1. You have 2 views which can be toggled using a parameter, one for 60 months the other for 36 months?
2. Your month field is of integer type and cannot be converted to date type in tableau?
3. And you want your data to load quickly since the query is taking too much time to load 60 months data?
1. How about creating an extract for the 60 months? That should help return values quickly. And you can setup refreshes for the extract on an incremental basis which will not harm the report performance.
2. Using a lookup filter might help you in achieving the desired output but I dont think it will make things quicker. You can still try your current LAST() and MAX() combinations
3. If you could by any chance convert the Months fields to a Date field it would make everything simpler in just one go. Also, by any chance can you tell what are some sample values for this field. Can you add year field to it in Tableau?
Thanks again for your detailed responses, thoughts, and inputs!
So you are saying that:
1. You have 2 views which can be toggled using a parameter, one for 60 months the other for 36 months? -Correct, I tried this as a work around to see if I could bypass querying all months of data. It didn't work, so this doesn't need to be in place.
2. Your month field is of integer type and cannot be converted to date type in tableau? -Correct again, when modified to date, it isn't readable, or make sense to a user.
3. And you want your data to load quickly since the query is taking too much time to load 60 months data? Correct, which is why we are trying to limit the number of months.
As for the proposed solutions, #3 is the way to go and I've begun the discussion to get this in motion. One unknown is that if we change to date, and we now use the date functions, we still don't know if it will query all months, or just the 36 months we are looking for. This is proper way to approach the issue and we push as much processing down into the database level to lighten the load on tableau.We will also be exploring structuring the data as such in the database to reach back and deliver the last 36 months when one month is selected, but also having the option to query 60 months.
For #1, I believe our data is too big to create an extract. The query that fetches 60 months of data is about 100GB and over 1 billion rows/records.Not only is the data high volume, but high dimensional as well....and this is with aggregations in place. I'd love to be able to create an extract, but just don't think it's in the cards.
Solution #2 I just don't think is feasible. Even if I get the calculations to perform correctly, and it doesn't hit all months, the performance trade off won't be worth it as you mentioned.
Sample data for Archive Month in integer format is the following:
Again, I can't thank you enough for your input and ideas. I believe we have some data prep work to do to gain this kind of functionality. Your analysis and troubleshooting has been spot on though!
1 of 1 people found this helpful
Another idea came to my mind regarding tuning your performance load. We can have 2 data sources. 1 data source will be the primary data source which will have only 36 months data. This will be used in all the sheets and dashboards. And since you want to show 60 months as well, so for that single view/graph you can point to a new(duplicate) data source which has 60 months of data.
Now when you assimilate everything on a dashboard you can put the 60 months view in a separate tab and all your 36 months together. This will at-least ensure that most of your views get loaded quickly. And in this scenario only the 60 months view should take extra time.
Another thing that you can try to do is construct the date field. Since you already have 20180430 format. You can create 3 separate calculated fields for Year, Month and Day. Then append them into 1 string value and parse them into date format.
Year: LEFT(ArchiveMonth, 4) = 2018
Day: RIGHT(ArchiveMonth, 2) = 30
Month: RIGHT(LEFT(ArchiveMonth, 6), 2) = 04
Now append them together DATE(STR(Day +'/'+ Month+ '/' + Year))
There might be syntactical errors in the above fields but I hope you do understand the reference I am trying to make.
Let me know if either solution works for you. Also, please mark helpful and/or correct to what ever responses worked for you.
Thanks again for the input! I definitely need to look into multiple data sources in hopes we can make it work. Everywhere else in the workbook needs access to the full 60 months of data via the live connection, but limit to a single selection archive month, so can look 60 months back at that particular month. We build our marts/views for the full 60 months, but wondering if we could build a view with 36 months and that would server as our data source for the tab showing 60 months of plotted data....so thank you for that suggestion. I could then use a parameter to show 36 vs 60 and duplicate the worksheets pointing to the primary data source of 60 months.
I've also tried constructing my own date, but I believe the live connection hinders that. I run into an error when building the month calc that I've researched and learned that it works within extract, but not live connection.