topic Re: Calculating current week - x weeks ago on time x-axis in QlikView

Calculating current week - x weeks ago on time x-axis

brian_booden — Wed, 15 Aug 2012 12:18:12 GMT

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?

Regards,

Brian

Re: Calculating current week - x weeks ago on time x-axis

swuehl — Wed, 15 Aug 2012 13:16:23 GMT

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

=sum(Sales) - above(sum(Sales),0,4)

Hope this helps,

Stefan

Re: Calculating current week - x weeks ago on time x-axis

brian_booden — Wed, 15 Aug 2012 15:03:28 GMT

Hi Stefan,

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.

Regards,

Brian

Re: Calculating current week - x weeks ago on time x-axis

swuehl — Wed, 15 Aug 2012 15:07:28 GMT

If your dimensions are called customer and week, try

=aggr(sum(Sales) - above(sum(Sales),0,4), customer,week)