5 Replies Latest reply: Nov 1, 2010 4:45 PM by robin007 RSS

    Restrict Pivot table to fixed grid based on two dimensions

    robin007

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

       

       

        • Restrict Pivot table to fixed grid based on two dimensions
          Neil Miller

          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.

            • Restrict Pivot table to fixed grid based on two dimensions
              robin007

              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?

                • Restrict Pivot table to fixed grid based on two dimensions
                  Neil Miller

                  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.

              • Restrict Pivot table to fixed grid based on two dimensions
                Stephen Redmond

                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

                 

                  • Restrict Pivot table to fixed grid based on two dimensions
                    robin007

                    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.