Restrict Pivot table to fixed grid based on two dimensions

[#1]

Any help would be gratefully appreciated as this is slowly driving me crazy... I'm trying to create a fixed 3x3 grid using a pivot table where the cells that are shown are the most recent based on selections made for the two dimension. Perhaps some actual data would help explain things better...

Table is as follows (sample data):

Snapshot_date	Bucket_date	Qty
01/01/10	01/01/10	1000
01/01/10	01/02/10	1000
01/01/10	01/03/10	1000
01/01/10	01/04/10	1000
01/02/10	01/02/10	1000
01/02/10	01/03/10	1000
01/02/10	01/04/10	1000
01/02/10	01/04/10	1000
01/02/10	01/05/10	1000
01/03/10	01/03/10	1300
01/03/10	01/04/10	1200
01/03/10	01/05/10	1000
01/03/10	01/02/10	1400
01/03/10	01/06/10	1000
01/04/10	01/04/10	1100

Bear in mind for the above table that the dates are in dd/mm/yy format and that the data I'm using (both test data and my live data)guarantees that the dates will always be the first of the given month.

I have a two dimensional pivot chart that shows Snapshot_date as the first dimension and Bucket_date as the second dimension.

What I need to achieve is limiting the Snapshot_date entries to no more than 3 rows (with the most recent 3 entries shown if more than 3 are within the selection) and the Bucket_date entries to no more than 3 columns. The difference with the Bucket_date entry is that it should be the same entries shown for Snapshot_date

I managed to restrict the first dimension quite easily and show the total for that 'cell' with:

sum({<Snapshot_date = {"=rank(Snapshot_date) <= 3"}>} Qty)

Now I need to make sure that the second dimension matches the same 3 values chosen for the first dimension. I thought the following would work (which it didn't)

sum({<Snapshot_date = {"=rank(Snapshot_date) <= 3"},Bucket_Ddate = {"=rank(Snap_date) <= 3"}>} Qty)

The successful result should look something like this (based on above data with no selections made)

Snapshot	Bucket	01/04/10	01/03/10	01/02/10
01/04/10		1100	-	-
01/03/10		1200	1300	1400
01/02/10		2000	1000	1000

But alas mine doesn't at the moment.... Mine has three rows but the columns just keep on going (because I'm jsut restricting the one dimension)

Any help or advice anyone can offer would be very much appreciated on this one..

[#2 · Accepted solution]

Try:

sum({<Snapshot_date = {"=rank(Snapshot_date) <= 3"},
 Bucket_date = P({<Snapshot_date = {"=rank(Snapshot_date) <= 3"}>} Snapshot_date)
>} Qty)

I'm not sure I would use Rank in this situation, but since Rank is working for you on your first expression, something like this should work.

EDIT: Fixed a typo in the expression.

[stephencredmond]

Hi,

You could achieve this with a couple of calculated dimensions using AGGR:

=Date(If(Aggr(Rank(Snapshot_date), Snapshot_date)<=3, Snapshot_date, null()))

and

=Date(If(Aggr(Rank(Bucket_date), Bucket_date)<=3, Bucket_date, null()))

Then set the "Supress when value is null" as true for both of them.

Your expression is then just a simple Sum(Qty).

Regards,

Stephen

[#4]

Worked like a charm! Thanks very much.

I can't find any documentation on the P() function - What does that do exactly?

Also, you mentioned you wouldn't have done it with a rank function - How would you have gone about it?

[#5]

The P() and E() functions are new to QlikView 9. I totally missed them until about a month ago and I haven't looked back since. P() means probable and E() excluded. In my example, it means set Bucket_date to the Possible values of Snapshot_date. As you can see, the P() function can contain it's own Set Expression.

In the QlikView 9 Reference Manual, check for a section entitled: Set Modifiers with Implicit Field Value Definitions. It's page 360 in book 3. Or simply search for P() and you'll go right to that page.

EDIT: I would have used Max instead of the Rank. You can use Max(Snapshot_date, 3) to give you the third highest value. Here would be the final expression:

sum({<Snapshot_date = {">=$(=Max(Snapshot_date, 3))"},
 Bucket_date = P({<Snapshot_date = {">=$(=Max(Snapshot_date, 3))"}>} Snapshot_date)>}
 Qty)

I don't think one is necessarily better than the other. My statement was more of a disclaimer meaning I wasn't exactly sure how your rank expression worked. There are usually a few different ways to tackle the same problem.

[#6]

I played about with something similar on calculated dimensions and came up with something similar to your suggestion. The only problem with it was because the bucket_date is a second dimension on top of snapshot_date it appears to rank within the dataset for that snapshot - Hope that makes sense!

I'm going to try and adapt your idea with the other one here because I'd prefer to use the dimension approach and keep the expression 'clean' (Don't know why, just one of those strange human desires....)

I'll keep you posted on the results.