Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Unrelated Pivot Dimension with Linked Data

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.

1 Solution

Accepted Solutions
Not applicable

Re: Unrelated Pivot Dimension with Linked Data

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.

6 Replies

Re: Unrelated Pivot Dimension with Linked Data

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.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Unrelated Pivot Dimension with Linked Data

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Unrelated Pivot Dimension with Linked Data

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.

Not applicable

Re: Unrelated Pivot Dimension with Linked Data

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.

Not applicable

Re: Unrelated Pivot Dimension with Linked Data

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

Community Browser