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

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

Tue, 10 Aug 2010 18:30:00 GMT

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

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

johnw — Tue, 10 Aug 2010 20:54:26 GMT

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)

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

Tue, 10 Aug 2010 21:13:35 GMT

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

--Dan

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

johnw — Tue, 10 Aug 2010 21:49:49 GMT

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")

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

Wed, 20 Jan 2016 19:45:11 GMT

Hi John,

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

Thanks in advance

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

israelcsouza — Wed, 20 Jan 2016 19:54:29 GMT

Attach your example QVW.