Hi all, thanks in advance for having a look at my problem. In a nutshell I want to chart the delta between each day of the last 7 day period and and the average of the week previous to the given day. For example:
CurrentDay = CurrentDay - avg(PrevWeek)
Sunday = Sunday - avg(PrevWeek)
Saturday = Saturday - avg(PrevWeek)
etc...
the PrevWeek would dynamically calculate using an offset that I've added to the date dimension.
I was trying to use a set for this but no luck. I can get the daily average value for the "previous week" just fine:
((sum({<WeekOffset={"$(=max(WeekOffset))"}>}MyMeasure))/count(distinct {<WeekOffset={'$(=max(WeekOffset)-1)'}>}DateSID))
However, as soon as I try to use that value in a chart it all goes wrong. I thought that I should be able to do something as simple as:
=((sum({<WeekOffset={"$(=max(WeekOffset))"}>}MyMeasure))-((sum({<WeekOffset={"$(=max(WeekOffset)-1)"}>}MyMeasure))/count(distinct {<WeekOffset={'$(=max(WeekOffset)-1)'}>}DateSID))
using the daily grain of the date dimension and then restrict the chart to show only the most recent 7 entries. However, what I get is the negative daily averages for the previous week. Basically the only thing resolving is the second part of the euqation. Moreover, the chart is filtering to show only the days where WeekOffset-1 matches. I've attached a screen shot showing the results. Please note that any smuding on the chart are a result of my tears and/or rage.
Keep in mind what I really want is the delta for the 7 most recent days against the average of all the numbers shown in the chart. Any advice would be greatly appreciated!
