topic How to: graphical visualization of a pivot table? in QlikView

How to: graphical visualization of a pivot table?

Anonymous — Wed, 02 Feb 2011 14:33:20 GMT

Hello Qlikview community,

I have a special question concerning pivot tables an the graphical visualization of them.

I do have a dataset, which looks as follows:


fallnr | DRG_2010 | DRG_2011 | D_CW
fallnr1 | I21Z | I21Z | 0,300
fallnr2 | I21Z | I31Z | 0,500
fallnr3 | I13D | I13C | 0,200

When I put this data into a pivot table, I get something like this:

As you can see, there ist for every DRG 2010 a record, which tells you, which DRG 2011 there is and how many of them.

Now I wanted to have that in a bar chart similar to a pivot chart in Excel. But what I get is this:

The information from the pivot chart is fully transfered to the bar chart. But it shows additionally all DRG_2011 records, that have no correlation to DRG_2010. The pivot chart does not eliminate the values, that are zero or do not have a correlation.

I already tried to check and uncheck the "do not show NULL values" but that did not help.

What I wanted to have is something similar to a pivot chart in excel like this:

As you can see, there are both dimensions shown in one axis and there are no records, which values are zero or null.

Do you have any suggestions, how to get this problem solved?

Thank you very much for any help,

Karsten

How to: graphical visualization of a pivot table?

Wed, 02 Feb 2011 18:48:57 GMT

Maybe you can add a new dimension (='' for exemple) in the bar chart.

But I'm not sure it improve your chart.

Check also "Suppress Value When Is Null" in the dimension properties.

Regards

How to: graphical visualization of a pivot table?

Wed, 02 Feb 2011 20:44:49 GMT

I would use a Calculated Dimension something along the lines of "=DRG_2011 & chr(10) & DRG_2010".

As a bonus, you could define the Background Color of the Expression as some RGB manipulation to group the DRG_2010 items together. I used =RGB(100*Mid(DRG_2010,2,1),mod(100*Mid(DRG_2010,3,1),255),mod(10*ord(Mid(DRG_2010,4,1)),255)) to group the I06 as blue, I09 as green, I10 as purple, etc.

How to: graphical visualization of a pivot table?

Anonymous — Thu, 03 Feb 2011 10:21:58 GMT

Wow, that's awesome! Qou made my day!

Thank you very much!

The only thing, I did not get to work for me is the formula for the grouped colors. Which is a very good suggestion.

I put the formula into the formula field for the first color in the color tab of the diagram. Is this correct? I always get black as the resulting color. Do I have to set something else? Or am I completly wrong?

Your picture proofes, that it's possible.

Thank you in advance!

Karsten

AW:Re: How to: graphical visualization of a pivot table?

Thu, 03 Feb 2011 10:40:53 GMT

Hello Karsten,


jweber1travelers wrote: you could define the Background Color of the Expression


kbessner wrote:I put the formula into the formula field for the first color in the color tab of the diagram. Is this correct?

No, it is a good idea but it is the "Background Color of the Expression". For this try instead --> chart properties --> expression tab --> expand the according expression via [+] --> choose "bg color" and input the formula from the post above into the Definition-Field.

AW:Re: How to: graphical visualization of a pivot table?

Anonymous — Thu, 03 Feb 2011 10:56:16 GMT

Oh yeah, that's it!

I never used it before. And I can see, that I still have much more to learn.

Thank you very much!

Kind regards,

Karsten