
1. Re: Moving average calculation
Joshua Milligan Feb 2, 2013 7:01 PM (in response to Cristina Palo)C Palo,
This is one of those questions that probably has a relatively easy answer, but it's a little hard to say without seeing what you are working with. Would it be possible to post a packaged workbook (even using dummy data if there's any sensitive info that can't be shared)?
Joshua

2. Re: Moving average calculation
Cristina Palo Feb 4, 2013 5:59 PM (in response to Joshua Milligan)Joshua,
Thanks for your help...please see attached the package workbook.

3. Re: Moving average calculation
Joshua Milligan Feb 4, 2013 8:16 PM (in response to Cristina Palo)1 of 1 people found this helpfulC Palo,
In looking at your data, it looks like the following calculation would determine whether a promotion was in effect:
[Week Has Promotion]
NOT ISNULL([PROMO_WKS$_Week Ended])
I created that field and then used it as a filter for the view. Since the filter is applied first, the table calculation will only be calculated for dates that did not have an ad.
I've attached the workbook so you can take a look.
Joshua

4. Re: Moving average calculation
Cristina Palo Feb 7, 2013 4:38 AM (in response to Joshua Milligan)Joshua,
Thank you very much for the quick response! There is one issue with the formula above  I need the baseline calculated for ALL weeks since I need a line chart of baseline (weekly). Any other good ideas??
Thanks very much!!!
Cristina

5. Re: Moving average calculation
Cristina Palo Aug 28, 2013 8:32 AM (in response to Cristina Palo)Still srtuggling with this issue...can anyone help?

6. Re: Moving average calculation
Matt Lutton Aug 28, 2013 8:54 AM (in response to Cristina Palo)Hi there,
We'd love to help, but I'm not sure I understand your problem or what you are trying to do. Jonathan was able to filter out the weeks you didn't wantwhat problem are you having with the resulting line chart in the workbook he posted?

7. Re: Moving average calculation
Cristina Palo Aug 29, 2013 10:55 AM (in response to Matt Lutton)By using a T/F I eliminate the weeks completely from the analysis. I still need to see the every single week on the charts, however the baseline calculation (which is a moving average of total dollars) should NOT include weeks where I run the add.
For example:
Total Dollars
Week 1 10 10 Week 2 12 12 Week 3 9 9 Week 4  PROMO 25 Week 5 11 11 Week 6 10 10 Week 7 8 8 12.14 10.00 I need the BASELINE for ALL 7 WEEKS to show 10 not 12.14. and I NEED to have week 4 present in the charts with Total Dollars 25 (one line chart) and Base Dollars 10 (separate line chart).
Is that a bit more clear?
Thank you SOOO much for helping
Cristina

8. Re: Re: Moving average calculation
Jim Wahl Aug 29, 2013 7:40 PM (in response to Cristina Palo)2 of 2 people found this helpfulHi Cristina,
Building on Joshua's formula, I think you can do this by adding a conditional on Total Sales Amount, which returns null for weeks when there was a promotion.
Your current Baseline Calculation formula is:
WINDOW_AVG(SUM([Total Sales Amount]),4, 4)
I created a new calculated field Total Sales Amount for Baseline
IF ISNULL(MIN([PROMO_WKS$_Week Ended])) THEN SUM([Total Sales Amount]) END
The first part is Joshua's logic, which seems to work well.
Now you can substitute this into a new Baseline Calculation. Since this is already an aggregate, it's just WINDOW_AVG(), not WINDOW_AVG(SUM()):
Baseline Calculation New =
WINDOW_AVG([Total Sales Amount for Baseline],4, 4)
I also added a Lift calculation.
( SUM([Total Sales Amount])  [Baseline Calculation NEW] ) / [Baseline Calculation NEW]
After adding this formula, make sure to change the number format to percentage (right click on the field in the left Data pane > Default Properties > Number Format.
Here's the result in tabular format. The promotion weeks are highlighted in red. The baseline calculation is a +/ 4 week moving average. In the case of a promotion week, the value is not included in the average.
Jim

9. Re: Re: Moving average calculation
Cristina Palo Aug 29, 2013 9:24 PM (in response to Jim Wahl)OMG I can't believe it worked Jim!!! I am so excited! Thank you so very much! I will be able to move all my promotion evaluations to Tableau! Thanks again!
Cristina

10. Re: Moving average calculation
Jim Wahl Aug 30, 2013 11:19 AM (in response to Cristina Palo)You're welcome!