Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Restrict Pivot table to fixed grid based on two dimensions

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..

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Restrict Pivot table to fixed grid based on two dimensions

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.

5 Replies
Not applicable

Restrict Pivot table to fixed grid based on two dimensions

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.

stephen-a_redmo
Valued Contributor II

Restrict Pivot table to fixed grid based on two dimensions

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

Not applicable

Restrict Pivot table to fixed grid based on two dimensions

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?

Not applicable

Restrict Pivot table to fixed grid based on two dimensions

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.

Not applicable

Restrict Pivot table to fixed grid based on two dimensions

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.

Community Browser