10 Replies Latest reply on May 11, 2018 7:25 AM by Rajesh Narasimha Murthy

6months rolling average calculation

Hi

I am looking for the calculated field showing the 15 months rolling average.

I have calculated value called  STP% with formula "SUM(IIF([type_auto]="STP" ,[COUNT],0))/  SUM([COUNT]". this gives the STP%.

if I bring months and drag stp%, I get the values correctly.

what I am looking at is, I want to get the rolling 6 months average of the percentages of STP% which I already calculated.

Example:

with the below table I want to see the last rolling six months of average of the STP% calculated.

Example: rolling six months average of STP% from Dec 2017 to May 2018 is 68.03%. like this I am looking for a dynamic rolling 6 months calculation. kindly help.

 Months STP% Oct-2017 69.87% Nov-2017 68.48% Dec-2017 66.20% Jan-2018 70.22% Feb-2018 67.19% Mar-2018 66.87% Apr-2018 65.64% May-2018 72.07%
• 1. Re: 6months rolling average calculation

good morning

have you tried          WINDOW_AVG([Profit Ratio], -6, 0)

jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: 6months rolling average calculation

window avg does not work. I tried already with the below formula

WINDOW_AVG([STP%],-6,0)

it's giving the result as 67.99% which looks wrong. if you try doing the average of the percentages mentioned above in the table, you get different result.

please try and let me know if you find different options.

• 3. Re: 6months rolling average calculation

sorry you are correct the

The formula should be as shown below

WINDOW_AVG([Profit Ratio], -5, 0)

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 4. Re: 6months rolling average calculation

This way, it works. but, I am looking to get just the number. not along with months.

I just want the 68.03% as an independent number to be shown, Not along with the months.

along with the months you get correctly. but, just the number shown is required. if I just drag the moving average calculation to the text I show 67.99% which is wrong. I want the number to be shown as 68.03%

suggest if you have solution

• 5. Re: 6months rolling average calculation

I guess i must have missed that in your post

Jim

• 6. Re: 6months rolling average calculation

yes, I apologies for not making it clear.

any solution?

• 7. Re: 6months rolling average calculation

see attached

the filter

forces the table calculation need to do the rolling average to be done first (out of order in the order of operations)

then you can filter on the date you want without affecting the calculation

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 8. Re: 6months rolling average calculation

This is giving me the error message as below

without additional filters, can't we get the desired results? because, I need to show this for a particular dimension once the solution is found.

• 9. Re: 6months rolling average calculation

first

you are trying to do a running average calculation - that is a table calculation in tableau

they are on the bottom of the order of operation

you wanted to show only the net result - but to get the result the table that underlies the viz needs to total range of dates

so to show only the last date the process is to use another table calculation (lookup) as a filter

to do that

change the filter to discrete

then

then

Now I understand that you want to not-filter the date to use for other calculations and yet have just the single value for the last date in the serires

the only way I know how to do that is to create 2 worksheets one with the filter and 1 without and bring them together in a dashboard

Jim

• 10. Re: 6months rolling average calculation

Thanks for taking the time on this to help. unfortunately, that won't work for my further actions on the workbook.

anybody else have any suggestions?