Is there a way to do period over period calculations without pre-computing the data?

[#1]

I've seen a few posts on here that are similar but essentially we are trying to create period over period charts (i.e. sales this week versus last week) and I'm not having much luck. We would rather not pre-compute this in the load scripts but instead create a calculation using set analysis. I would assume there is some sort of default function to leverage but I haven't figured it out if there is.

Can anyone provide the syntax for calculating period over period calculations?

Thanks in advance,

Dan

[johnw]

Set analysis on its own only works when you've selected a single week to compare to because the set is built once for the entire chart, not once per row (per week). But if that's all you need, and let's say your week is the Monday date in MM/DD/YY format:

dimension = Week
sales this week = sum(Sales)
sales last week = sum({<Week={'$(=date(Week-7,'MM/DD/YY'))'}>} Sales)

The actual code will depend on how you define your weeks, though. And like I said, it only works when you've selected one specific week. Otherwise, Week has multiple values, which evaluates to null, which won't match any weeks, so your sum will be 0 or null, I forget which. So I normally suggest an As Of table:

As of Week, Week Type, Week
08/09/10, This Week, 08/09/10
08/09/10, Last Week, 08/02/10
08/02/10, This Week, 08/02/10
08/02/10, Last Week, 07/26/10
...

You'd load that from your actual data, not inline like this, and you'd define your weeks however you want. Then the simplest way to display the results is with a 2D pivot table:

dimension 1 = As of Week
dimension 2 = Week Type
expression = sum(Sales)

But if you wanted a straight table instead, you could do this:

dimension = As Of Week
sales this week = sum({<"Week Type"={'This Week'}>} Sales)
sales last week = sum({<"Week Type"={'Last Week'}>} Sales)

[#3]

Is it possible to do this without changing the load scripts or the time dimension?

--Dan

[johnw]

Well, if you can suffer with the limitations that the first selected week won't have a previous week value calculated, and that your chart must always be sorted by week (disallow interactive sort), you should be able to use above() to get what you want. I believe it would look like this:

dimension = Week
Sales This Week = sum(Sales)
Sales Last Week = above("Sales This Week")

[#5]

Hi John,

               When you say sales last week = above("sales this week"). Do you mean above(sum(sales))?

Thanks in advance

[israelcsouza]

Attach your example QVW.