topic Previous Period in QlikView

Previous Period

Wed, 23 Sep 2009 21:41:37 GMT

Hi,

I've got following datasources:

[CODE]Data:
LOAD * INLINE [
Period, User, Value
P1, User1, 50
P1, User1, 50
P2, User1, 200
P2, User2, 200
];

Calendar:
LOAD * INLINE [
Period, PrevPeriod
P2, P1
];
[/CODE]

I want to produce following Pivot Table in a report:

Period	User	Value Sum on Current Period	Value Sum on Previous Period
P1	User1	100	0
P2	User1	200	100
P2	User2	200	0

As you can imagine, i've got problems generating data for the "Value Sum on Previous Period". I'm trying to use "Set Analysis" functionnality but without success by now.

Help would be appreciated.

Thanks in advance.

Previous Period

Wed, 23 Sep 2009 22:50:58 GMT

A solution is to use the expression

above(sum(Value))

for your 'Value Sum on Previous period'

Previous Period

Wed, 23 Sep 2009 23:28:17 GMT

It doesn't seem to produce expected behaviour:

Previous Period

Thu, 24 Sep 2009 00:13:35 GMT

I've been struggling with the exact same issue for 2 days now.

Previous Period

Thu, 24 Sep 2009 02:32:32 GMT

anyone? anyone? Bueller? Bueeellleerrr?

Previous Period

Thu, 24 Sep 2009 04:23:23 GMT

I don't have a good solution, but I figured out a few things that may be worth it to share.

The reason Above(Sum(Value)) does not work on your sample, is because of the order of the dimensions. According to the documentation,

If the table or table equivalent has multiple vertical dimensions, the current
column segment will include only rows with the same values
as the current row in all dimension columns except for the column
showing the last dimension in the inter field sort order.

Since Period is not the last dimension in the sort order, it is looking for the Above record where Period matches. In this case, you're getting the Above for P2 (200). If you switch the order and put User first, then you would get what you are looking for. Note: In a straight table, you can display Period as the first dimension, but put User first in the sort to get this to work. In a pivot, the only way is to put User as the first Dimension on that tab.

I tried to put User first in the Sort Order and then somehow sort by Period instead using the Sort By Expression, but I couldn't get it to work. That may be a possibility. EDIT: That is not a possibility, when you use a Sort By expression to sort by Period, the Above() function goes back to looking for matching Periods. If you can sort by User first, then problem solved, but that may not be ideal.

I tried quite a few other things with no luck. My advice would be to see if you can get the aggregate the data in your load and add PrevValue to each record.

Previous Period

Wed, 30 Sep 2009 16:36:11 GMT


NMiller wrote:
My advice would be to see if you can get the aggregate the data in your load and add PrevValue to each record.

Thanks for your time on this subject.

If I use datetime formats instead of strings for the "Period" field, is there a way to solve this issue ? I'm looking for an equivalent to the ParallelPeriod MDX method.

Previous Period

Wed, 30 Sep 2009 21:21:27 GMT

I've never used the ParellelPeriod method, but it looks interesting. I'm not sure if you can use MDX in QlikView loads.

If you're looking to do something on the load script, you should be able to do it using QlikView functionality. Here is a sample that works using Inlines similar to your sample:

Data:
LOAD * INLINE [
 Period, Amount
 P1, 50
 P2, 60
 P3, 70
];
Calendar:
LOAD * INLINE [
 Period, PrevPeriod
 P1,
 P2, P1
 P3, P2
];
DataPrev:
LOAD Period As PrevPeriod, Amount As PrevAmount
RESIDENT Data;

I'm sure similar logic could be applied to loads from a database or other source.

Also, check out another recent thread that is trying to do something similar. The solution in that thread seemed to be to handle it in the load, so you may get some ideas: http://community.qlik.com/forums/t/20915.aspx