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)?
1 of 1 people found this helpful
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.
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!!!
Still srtuggling with this issue...can anyone help?
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 want--what problem are you having with the resulting line chart in the workbook he posted?
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.
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
2 of 2 people found this helpful
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.
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!