Calculating current week - x weeks ago on time x-axis
I'm trying to create a line chart that shows Sales this week - Sales 4 weeks ago as a single expression on x-axis of time (week). Plotting the Sales for the week on the x-axis is simple, that's just
Sum(Sales)
But i am having trouble with creating the expression that brings in the Sales from 4 weeks ago. I am trying to use set analysis. I would expect this to be something like, but this expression does not work:
Sum(Sales) - Sum ({$<WkNo={$(vWeekNo4WeeksAgo)}>}[Value (£)])
The variable vWeekNo4WeeksAgo is set up to get the week from 4 weeks ago as per below:
vWeekNo4WeeksAgo = (Num(WkNo) - 4)
I am wondering whether this is something i can do with set analysis when mapping against a time axis? Or do i need to do this sort of thing in the load script to do a mapping to the sales values as they were 4 weeks ago?
The set analysis is only evaluated once per chart while I assume you want to calculate per dimension value (Week),i.e. today's week - 4 weeks back / last week - 5 week back.
You can probably use chart inter record functions to get what you want, e.g. using above(), below() etc., maybe like
As a simple example, this really helps, and it works - thank you!
The next stage is that i need to map on a 2nd dimension, so that i can view the sales differential history for each customer. When i try to add the 2nd dimension, unfortunately this does not work. It works with a simple expression, but when i introduce the Above() into the expression, it does not load.
I have a feeling i may need to do this a different way.
