topic Re: How to display previous period values on pivot table in QlikView

How to display previous period values on pivot table

Tue, 22 Jan 2013 16:49:57 GMT

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

Re: How to display previous period values on pivot table

Anonymous — Tue, 22 Jan 2013 16:57:01 GMT

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

Re: How to display previous period values on pivot table

Wed, 23 Jan 2013 09:53:04 GMT

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

Re: How to display previous period values on pivot table

er_mohit — Wed, 23 Jan 2013 10:33:17 GMT

hiii

try this

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

Re: How to display previous period values on pivot table

Wed, 23 Jan 2013 11:40:12 GMT

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?

Re: How to display previous period values on pivot table

Wed, 23 Jan 2013 15:32:32 GMT

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

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