You can achieve this using a Table Calc filter.( when we use Table calculation that will apply only to the query result)
Please find the sample workbook attached, I have created a table calculation as Month then using it for filtering.
please let me know if you have any questions.
Table Calc Filter.twbx 962.8 KB
the filter on Month calculated field which you have created is discrete. Can you show me an example how you would do this example if I want to show in workbook the last 36 months. (on moving total).
Thanks Indumon I think you nearly got it. The only thing missing is that I need relative months and not months filtered in a fixed way. So next month when I get new data I want to have a back relative 36 month period, so that always the latest month is included in the analysis. Is this possible?
Thanks a lot
I think possible, I will upload a working sample
Here you go!, I have updated the calculation and placed the field in filter.
If min([Order Date])>= Dateadd('month',-36,(datetrunc('month',today()))) And min([Order Date])< datetrunc('month',today()) then Lookup(Attr(DateName('year',[Order Date])),0) + '-' + Lookup(Attr(DateName('month',[Order Date])),0) else Null End
When you put the calculated field month in filter please check "Exclude" option and check "null" in filter, So last 36 months filter will happen automatically every month.
If you want November (Current Month) also included then change the calculation slightly to
If min([Order Date])> Dateadd('month',-36,(datetrunc('month',today()))) And min([Order Date])<= today() then Lookup(Attr(DateName('year',[Order Date])),0) + '-' + Lookup(Attr(DateName('month',[Order Date])),0) else Null End
Worked like a charm! Thanks a lot Indumon.
Glad it worked, you're welcome!
Indumon, I am having some problems when using your formula across different data sources. When using fields from the other source I get 'An error occured while communicating with the data source' - No such column. I am attaching a simple example here, maybe you have some ideas what might be wrong? The problem occurs when you put field Product type under rows.
Example.twbx 29.6 KB
When you do the data blending in Tableau, secondary data source fields will get aggregated automatically.
So the single source calculations needs to be revisited (convert to aggregate functions), if you are including secondary data source field.
Unfortunately I am not able to get the error from the provided workbook. Can you attach a workbook with error with your formula?. so I can have a better idea
Another suggestion is avoid data blending and try Join tables, if that is possible.
All secondary fields are aggregated. Dimensions are aggregated as Attributes (ATTR), which means that if there’s only one member it will show the member value but if there are multiple members it will show an asterisk.
I'm using 8.0.5.
I see on the screenshot that it works normally for you - very strange. I've recreated the formula but nothing has changed. This error only occurs when you use the non-primary field from the secondary data source in combination with the 'month' calculation in filter.
The formula is the same as in workbook:
If min([Time - Month])> Dateadd('month',-36,(datetrunc('month',today()))) And min([Time - Month])< datetrunc('month',today()) then
Lookup(Attr(DateName('year',[Time - Month])),0) + '-' + Lookup(Attr(DateName('month',[Time - Month])),0)
Can you please open a case with Tableau Support? ( attach your workbook as well).
I will also explore mean time.