Tableau : How to get the Dashboard always display the latest 10 days worth of group statistics

Question

My input text source always contains last 12 months worth of data. e.g: Current month is October. So My input source contains data starting from last Oct 1st to till date. But I want the aggregate statistics to be displayed on a daily basis for last 10 days of sales , 30 days of sales, 45 days of sale per product across various regions

I am trying to use window_avg fuction with something like window_avg(sum(sales), first() + datediff('day', window_min(min([date]))-1, dateadd('month',1,window_min(min([Date]))-1)) * 13,13) something like that. But I am not able to crack the exact logic.

Could you please suggest me some better way to achieve this, rather than using these kind of calculations. Also I am afraid if this goes wrong if there is data missing in the middle one or two days.

Any help is appreciated.

Answer 1

A very simple thing is to use a relative date filter. There's a UI for you to select they last N days.

Put the date on the columns shelf and set it to the date truncation of year-month-days. Put your measure row shelf. Put the date pill on the filter shelf too and use a relative date filter.

If you are doing simple aggregate like the sum of sales for a day it's easy and you'll not need to do anything else. You can can also fairly easily create a table calculation by right clicking on the measure and choosing one of the quick table calculations. Even when I'm doing a more sophisticated calculation, I start with a quick table calculation and then start editing.

If you are doing something like a moving average, the filter and the moving average can interact. For example, if I'm showing a 5 day trailing moving average over 30 day period, the first few days do not get averaged in the same way -- you don't have days over 30 days ago. If that's not really an issue for you, that's cool and you are done.

If it is an issue, it's going to be trickier. I'd suggest creating a second filter based on a table calc. The reason is the order of operations in Tableau. The raw data is filtered then aggregated by the database, then the table calcs are performed. If there are any filters on table calculations, then they are filtered after that. So basically, in my example, you want create a filter for 35 days on the date, then create a table calc on the date -- like using the INDEX() function. Filter the index function to show 30 days worth, then you've got a moving average that uses 35 days to compute the average, but only shows 30.