12 Replies Latest reply on Feb 22, 2016 10:42 AM by Vishal Sarkar

# How to calculate moving average and moving sum based on year selection in filter

Hi,

I am new to tableau. I would like to understand about one of the calculated metric that I am using. So I need to calculate the average and sum of a metric which should change dynamically based on years selection done in filter. For eg : if i select 2007-2009 then it should show average and sum for 3 years only and likewise. I have made a first crack by identifying the function to be used i.e window_avg and window_sum. So I am doing window_avg(sum(metric),"start","end") and window_sum(sum(metric),"start","end"). I am not sure what should I put in "start" and "end" parameter here.

Can someone guide me this. Thanks in advance!

Regards,

Vishal

• ###### 1. Re: How to calculate moving average and moving sum based on year selection in filter

Hi Vishal-

Couldn't you just use the sum and avg aggregation that would update dynamically when you filter?

If you need to use the window_sum/avg the start and ends can be omitted and it will use the entire partition or you could specifically define the offsets using a number -/+ or using first() last().

For example:

computes the average of all rows in the partition

WINDOW_AVG(avg([Sales]))

computes the average of the previous and the current row

WINDOW_AVG(avg([Sales]),-1,0)

computes the average of the first row in the partition through the current row

WINDOW_AVG(avg([Sales]),first(),0)

Hope that helps.

1 of 1 people found this helpful
• ###### 2. Re: How to calculate moving average and moving sum based on year selection in filter

Thanks a lot for the help. There are two more things I would have concerns with. I need to show my metric value in millions but once I select Million number format, there is "M" written with the value which I wanted to remove. Let me know how to do that.

Also, I was creating a heat map, below is the screen shot for the same. My heat map color coding is done based on a calculated field. But the value shown in heat map is a different measure. I am able to successfully implement the color coding for the same but the the color coding is done on text and I need the background to be color coded. Let me know if anyone knows how to do the same.

Looking forward for some help. Thanks in advance...

• ###### 3. Re: How to calculate moving average and moving sum based on year selection in filter

You can remove the M unit

Remove the M

To color code the background, change the mark type to Bar.  Put MIN(Number of Records) on Size.  Dial the size of the bar all the way up.  Put your measure on label. Then your color logic on color.

• ###### 4. Re: How to calculate moving average and moving sum based on year selection in filter

Thanks a lot for help. But if I remove M like that then the number displayed is like the original form i.e the number is not displayed in million. I think i might need to create an extra calculated field and divide the measure by 10^6 and then show it in label.

Also in case of color coding, I think we can use square instead of bar and then resize them to get something like the below mentioned screenshot :

But somehow when I try to resize the squares are of different size. I am assuming the size of square depends on the value of measure... As in my case the color coding is from different calculated metric and the value shown in label is a different measure. See the below screenshot :

Let me know how we can correct the above issue.

• ###### 5. Re: How to calculate moving average and moving sum based on year selection in filter

Hey Vishal-

If you first format the number in Million units and then remove the M in the custom format, you get the desired result without creating a new field.  The Sales total from my screenshot above is \$2,300,000.

The problem with the squares is that they will usually overlap because you are drawing a rectangle.  There are lots of ways to do things in T so maybe you found a way that works.

I attached a workbook for you to look at the difference between bars vs. squares and the sales formatting.  9.2.

• ###### 6. Re: How to calculate moving average and moving sum based on year selection in filter

Hi,

I tried using bar but it just displays a line and not boxes. Also could you please upload the workbook again. The current one is opening properly.

• ###### 7. Re: How to calculate moving average and moving sum based on year selection in filter

What version are you using?  If not 9.2, you can hack it back.

• ###### 8. Re: How to calculate moving average and moving sum based on year selection in filter

Good Day!

Hi @VISHAL SARKAR,, use Square instead of the bar.  Heat map required 1 or more dimensions and 1 or 2 measures.

This will automatically enable in the show me portion if you choose the right fields on your workbook.

Thanks and God Bless,

Regards,

Jonathan

• ###### 9. Re: How to calculate moving average and moving sum based on year selection in filter

Thanks a lot for help. I was able to implement the same

Was just curious to know is it possible to restrict the selection in filter in tableau ?

For eg I have 20 countries in a filter and I can select 5 or 6 at max then a pop should appear that I cannot select more than this.

• ###### 10. Re: How to calculate moving average and moving sum based on year selection in filter

There is no option to limit the filter to a # of selections, but there are a couple work-arounds using multiple parameters or a table calc to blank out the view.

• ###### 11. Re: How to calculate moving average and moving sum based on year selection in filter

ok thanks

1 of 1 people found this helpful
• ###### 12. Re: How to calculate moving average and moving sum based on year selection in filter

Hi,

Is it possible to create something like this :

The blue colored cells are not conditionally formatted. They come from a single metric. Let me know if we can combine this blue colored column and row with the heat map. Thanks in advance