6 Replies Latest reply: Mar 19, 2013 8:20 AM by Steven Bain RSS

    Unrelated Pivot Dimension with Linked Data

    Steven Bain

      Hiya Guys,

       

      I have a complex one so forgive me if this isn't clear - if absolutely necessary I'll create an example but I just don't have time at the moment.

       

      I have data in a table which represents a Risk Matrix:

       

      SEVERITYLIKELIHOODAREACOLOURWEIGHT

      Severity 1

      Unlikely - Rarer than once every 15 yearsCritical area25575
      Severity 1Rare - Once between every 15 years and 5 yearsCritical area255150
      Severity 1Likely - Once between every 5 years and 2 yearsCritical area255225
      Severity 1Highly - Once between every 2 years and once a yearCritical area255300
      Severity 1As good as certain - Multiple every yearCritical area255375
      Severity 2Unlikely - Rarer than once every 15 yearsSerious area6553525
      Severity 2Rare - Once between every 15 years and 5 yearsSerious area6553550
      Severity 2Likely - Once between every 5 years and 2 yearsCritical area25575
      Severity 2Highly - Once between every 2 years and once a yearCritical area255100
      Severity 2As good as certain - Multiple every yearCritical area255125
      Severity 3Unlikely - Rarer than once every 15 yearsLess serious area6528010
      Severity 3Rare - Once between every 15 years and 5 yearsSerious area6553520
      Severity 3Likely - Once between every 5 years and 2 yearsSerious area6553530
      Severity 3Highly - Once between every 2 years and once a yearSerious area6553540
      Severity 3As good as certain - Multiple every yearSerious area6553550
      Severity 4Unlikely - Rarer than once every 15 yearsLess serious area652805
      Severity 4Rare - Once between every 15 years and 5 yearsLess serious area6528010
      Severity 4Likely - Once between every 5 years and 2 yearsLess serious area6528015
      Severity 4Highly - Once between every 2 years and once a yearSerious area6553520
      Severity 4As good as certain - Multiple every yearSerious area6553525
      Severity 5Unlikely - Rarer than once every 15 yearsLess serious area652801
      Severity 5Rare - Once between every 15 years and 5 yearsLess serious area652802
      Severity 5Likely - Once between every 5 years and 2 yearsLess serious area652803
      Severity 5Highly - Once between every 2 years and once a yearLess serious area652804
      Severity 5As good as certain - Multiple every yearLess serious area652805

       

      By creating a Pivot Chart from this data I can create a 5x5 table, containing (for arguement's sake) "WEIGHT" data with each Cell the appropriate COLOUR:

      Matrix_Weight.png

       

      My issue is that, to create this Matrix, I cannot link the data (above) to my base data (ie: holding Dates and Events) - as if a Selection is made which does not include one of the 5 SEVERITIES or 5 LIKELIHOODS then I cannot create the full Matrix.  I can obviously select the "Show all Values" and deselect "Supress When Value is Null" Chart options - but then I get extra "blank" rows and columns - plus I then no longer have access to all Cell COLOURS, eg:

       

      Matrix_Data.png

       

      Of course this means that (if I make the data above a "data island") when I attempt to COUNT the number of Events for each Cell - ALL data is returned, regardless of the current Selection.

       

      I am currently toying with the idea of a hybrid of the two implementations where I use a "data island" version of the table for the 'background' (with COLOURS) and then a transparent 'foreground' Pivot Chart using a linked table for the currently Selected data... however, I would like to show Totals (without having the "blank" rows and columns shown above - which, by viewing the table data highlighted above, I can understand why these are present).

       

      Am I missing some fundamental data concept?!

       

      How can I create an unrelated Pivot Chart which displays linked data? (or how can I remove the "blank" rows and colums from my Pivot Chart?).

       

      Any help will be greatly appreciated!

       

      Cheers,

       

      Steve.

        • Re: Unrelated Pivot Dimension with Linked Data
          Gysbert Wassenaar

          If you're using QV11 you can likely use alternate states. Create a new alternate state and assign that to your pivot chart. That way selections in the default state won't influence the pivot chart.

            • Re: Unrelated Pivot Dimension with Linked Data
              Steven Bain

              Hiya Gysbert,

               

              Thank you for your response - however if I use an Alternate State (which, from my understanding, would be equivelant to the "Data Island" implementation, above) my Pivot Chart would no longer be able to display a COUNT of all Cases related to the current Selections?

               

              Any other ideas?  :-)

               

              Cheers,

               

              Steve.

                • Re: Unrelated Pivot Dimension with Linked Data
                  Gysbert Wassenaar

                  See attached example. It uses two expressions using different states. One is 'hidden' by making the text transparant. It's no ideal, but I'm out of ideas for the moment. With a macro you could even set the column width of the second expression to zero.

                    • Re: Unrelated Pivot Dimension with Linked Data
                      Steven Bain

                      Hiya Gysbert,

                       

                      Very creative solution!  :-)  I've had a play but unfortunatelty it's not a complete fix:

                       

                      Matrix_Data_AS.png

                       

                      As you can see - I still don't have access to the COLOUR for the Cells which aren't populated (and the extra heading for the Expressions/extra Cell borders is causing presentation issues).

                       

                      I believe I may have found a "hack" by transposing the current linked data over an unrelated template (background) Pivot Chart with the Cell COLOURS.  If this work then I will post the result here - but if you have any further ideas please let me know!

                       

                      I appreciate your efforts and it was interesting playing with Alternate States  :-)

                       

                      Cheers,

                       

                      Steve.

                      • Re: Unrelated Pivot Dimension with Linked Data
                        Steven Bain

                        The solution I have come to is to create a copy of the Matrix table as a "Data Island" - which ensures I can display all rows and columns (with colours - regardless of current Selections):

                         

                        Matrix_Tables.png

                         

                        I have then created two Pivot Charts - one static (using the "Data Island" as a background), and another to display the current data.  To display the Totals I have created individual Text Objects which reference a function (variable) to calculate the number of Events per row and per column (making use of Set Analysis):

                         

                        Matrix_Separate.png

                         

                        With the "data" matrix having a transparent Cell background - transposing one over the other provides the desired result:

                         

                        Matrix_Both.png

                         

                        This is a little bit of a "hack" - but at least I have come to a solution  :-)  Hopefully this post will help someone in the future.

                         

                        Cheers,

                         

                        Steve.

                        • Re: Unrelated Pivot Dimension with Linked Data
                          Deepak Mallick

                          PLEASE HELP!!! ITS A BIT URGENT, THANKS! IN ADVANCE

                          equipment.png

                          My issue is the same to remove the null values without affecting the table structure.The solution lies in the attacment which u have provided above

                          Gysbert Wassenaar wrote:

                           

                          See attached example. It uses two expressions using different states. One is 'hidden' by making the text transparant. It's no ideal, but I'm out of ideas for the moment. With a macro you could even set the column width of the second expression to zero.

                          Can u plz let me know the step by step procedure of creating the below table which u showed in the "comm75252.qvw" bcoz my problem is the same .Plz see the above imageoutput.png