
1. Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 6, 2013 12:36 AM (in response to Gorazd Ozimek)Hi Gorazd,
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.
Lookup(Attr(DateName('month',[Order Date])),0)
please let me know if you have any questions.

Table Calc Filter.twbx 962.8 KB


2. Re: Moving sum calculation does not take into account values outside the time filter range
Gorazd Ozimek Nov 6, 2013 12:50 AM (in response to . Indumon)Hi Indumon,
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,
Gorazd

3. Re: Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 6, 2013 1:12 AM (in response to Gorazd Ozimek)Is it possible to post a packaged work book?
Please find the updated sample, I have added last 36 months filter in the calculation.

Table Calc Filter.twbx 969.5 KB


4. Re: Re: Moving sum calculation does not take into account values outside the time filter range
Gorazd Ozimek Nov 6, 2013 1:28 AM (in response to . Indumon)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

5. Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 6, 2013 1:32 AM (in response to Gorazd Ozimek)I think possible, I will upload a working sample

6. Re: Re: Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 6, 2013 2:05 AM (in response to Gorazd Ozimek)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.
Output.
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

Table Calc Filter.twbx 969.5 KB


7. Re: Re: Re: Moving sum calculation does not take into account values outside the time filter range
Gorazd Ozimek Nov 6, 2013 3:48 AM (in response to . Indumon)Worked like a charm! Thanks a lot Indumon.

8. Re: Re: Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 6, 2013 3:54 AM (in response to Gorazd Ozimek)Glad it worked, you're welcome!

9. Re: Re: Re: Re: Moving sum calculation does not take into account values outside the time filter range
Gorazd Ozimek Nov 7, 2013 12:16 AM (in response to . Indumon)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.
Thanks!
Gorazd

Example.twbx 29.6 KB


10. Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 7, 2013 12:37 AM (in response to Gorazd Ozimek)Hi Gorazard,
Welcome back!
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.
Data Blending:
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.

13. Re: Moving sum calculation does not take into account values outside the time filter range
Gorazd Ozimek Nov 7, 2013 12:51 AM (in response to . Indumon)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 nonprimary 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)
else
null
End

14. Re: Moving sum calculation does not take into account values outside the time filter range
. Indumon Nov 7, 2013 12:57 AM (in response to Gorazd Ozimek)Can you please open a case with Tableau Support? ( attach your workbook as well).
I will also explore mean time.
thanks