Flipping Axis on Pie Chart?

[#1]

I have a table with data similar to:

Object	Data1	Data2	Data3
Chair	10	5	5
Lamp	15	13	2
Basket	8	12	4
Shade	19	17	6
Book End	18	15	9

The problem is that I want it to show separate pie charts for Data1, 2 and 3 with a slice for each object, rather than the reverse, which is how it creates it now.

There is no "rotate axis" on pie charts, and I cannot create a duplicate of the table with the fields rotated around because of the sheer volume of data I am dealing with.

Suggestions (or did I miss some setting that can do this)?

It would be really nice if there was a standard "Rotate Axes" checkbox regardless of the type of chart you are displaying.

[#2]

John,

I have uploaded this sample so you can see what I am trying to accomplish.  

Yes, you are correct, I can create separate charts for each DataX, but I was hoping to use the trellis feature to contain all the charts and one legend without having to deal with each one independently.

Edit:  I forgot to mention that I have 9 metrics and I was trying to take advantage of the scroll bar that you get with trellis charts.   It goes away if you build them individually.   The legend also presents itself as an issue when you individualize them.

[swuehl]

Ok, that was chatty enough

I think I just missed the word "trellis" in your OP.

I don't think that flipping axis (or keeping the cross table schema) is easily possible with QlikViews technical design.

That's why there is a function to convert cross tables during load, but I understood that this is not what seems feasible in your case.

Sorry if all this is not much of a help.

Stefan

[swuehl · Accepted solution]

One more idea:

you could use a calculated dimension:

=valuelist('Sales', 'Cost', 'Profit')

as first dimension, object as second and as 1 expression a check for valuelists type and getting the correct field.

See attached sample app.

This is only feasible, if you know your data fields...

Have a nice evening,

Stefan

[johnw]

To do a trellis requires a dimension.  So the only way you can do this with a trellis chart is by making Data1, Data2 and Data3 the values of a dimension.  The way to do that is to use a crosstable as Stefan suggested.  Our concern is with the vast amount of data you have, and not wanting to greatly increase it.  But you wouldn't need to crosstable the entire data set, just an ID and the DataX fields.  I'm not sure if it will be practical to add a 60 million row table, but the row size for each row should at least be very small.  Let's say you autonumber or recno() your ID.  You'll have 20 million sequential integer IDs.  That should be 3 bytes each or less.  One byte each or less for your field names.  I'm not sure what your field values will compress to, but surely they will fit in 6 bytes or less compressed.  So let's say you're no more than 10 bytes per row, 60 million rows, so 600 million bytes.  It's extra data, but 14.6 vs. 14 GB might be acceptable, and I also may be overestimating.  For that matter, if you don't need the original data in the original table, I'm betting it takes basically the same 14 GB.  Or for that matter, even if you do, if QlikView's compression is clever enough, it might not add much, since the two tables store the "same" information.  In any case, I think it's worth a test to see what happens to your memory requirements and your load time.

The attached example shows the idea.  I'm using recno() for my IDs in this case, and dropping from the original table.

Ah, I see you have 9 fields instead of three, so now we're talking about an extra 1.8 GB, maybe.  Or maybe it'll be less.  I still think it's worth a test.

[johnw]

swuehl wrote:

you could use a calculated dimension:

=valuelist('Sales', 'Cost', 'Profit')

Ah, of course!  And you can simplify the expression:

pick(match(valuelist('Sales','Cost','Profit'),'Sales','Cost','Profit'),sum(Data1),sum(Data2),sum(Data3))

I like it.  That's probably better than a crosstable load for this case.

[#7]

All,

Wow!  That was a flurry of input!    Thanks to everyone who chimed in here and I really appreicate the quick responses.   You are all fantastic.

I think I am going to have to do some creative designing of the page here and see what I can do about getting all 9 metrics to show up as individual pie charts.   Perhaps with some buttons to hide and show various groups of three.

I would send you all a beer but I couldn't post it.   Perhaps a beer emoticon   [%]D   will work instead?  

[#8]

Ingenious!    Now THAT is what I call creative!

Thank you both for an elegant solution!

[swuehl]

I am glad we finally got a solution together.

Hm, I just noticed that the pop up info shows 'Sales' regardless if I hover over Cost or Profit.

Never mind, enough for today. I just opened a bottle of beer.

Cheers,

Stefan

[jhon341]

Wanting separate pie charts for Data1, 2, and 3, each with a slice for every object. Unfortunately, pie charts don't have a rotate axis option, and duplicating the table is impractical due to the large volume of data. It would indeed be helpful to have a standard "Rotate Axes" checkbox for all chart types. I'll make a note of your suggestion, Ashbourne lamp, and hopefully, future updates will address this.