How to display previous period values on pivot table

[#1]

Hello all, I'm new to QlikView and I'm trying to do something that in theory should be very straightforward but I can't seem to understand how to do it:

Let's consider this table that shows two columns: Reporting Period, Previous period responses:

Right now, I'm using the following expression:

=Sum({$<[Reporting Period Name]=,[Reporting Fiscal Year]=,[Reporting Period Key]={295}>}[Active and Unapproved Risks])

295 corresponds to Period: 2012/13-P09. What I really want is to show 2247 against period 2012/13-P10.

I don't want to use the above function, has there might be some filters that only show one of the rows.

Is there some set analysis or complicated expression that allows me to do what I want? Something like:

=Sum({$<[Reporting Period Key]={[Reporting Period Key] - 1}>}[Active and Unapproved Risks])

Thanks,

Joao

[Anonymous]

You could create another field on your calendar that has the previous period listed as a field on the record for each date and use that as a dimension.

You'd need to mark the table as Read only though (General properties) so the dimension is not selected causing future confusion.

Jonathan

[#3]

Hi Jonathan,

Thanks for your reply. Although your solution might work, what I am really interested is on doing this using the front end, as in this project I am very limited in the types of changes I can make to the load script.

Joao

[er_mohit]

hiii

try this

=Sum({<[Reporting Period Key]={"$(=([Reporting Period Key]) - 1)"}>}[Active and Unapproved Risks])

[#5]

Hi there, thanks for the reply.

Here's what I get with that expression.

With no period selected:

When I select period key 296:

Any help?

[#6 · Accepted solution]

I was able to do what I wanted with this expression:

=Aggr(Above(sum( {1} [Active and Unapproved Risks]),1,RowNo()), [Reporting Period Key])