17 Replies Latest reply: Aug 2, 2011 6:39 PM by Stefan Wühl RSS

    Flipping Axis on Pie Chart?

      I have a table with data similar to:

      ObjectData1Data2Data3
      Chair1055
      Lamp15132
      Basket8124
      Shade19176
      Book End18159

       

      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.

        • Re: Flipping Axis on Pie Chart?
          Stefan Wühl

          If I use Object as dimension and Data1 as expression, I get slices for each object with Data1 values as expected.

           

          Could you explain a bit more what your issue is or could you upload a sample app?

            • Re: Flipping Axis on Pie Chart?

              Turn on trellis and what you get is a pie chart for each object with slices for Data1, Data2 and Data3.   I need it the other way around.

                • Re: Flipping Axis on Pie Chart?
                  Stefan Wühl

                  You could be a bit more chatty in describing what your are doing (trellis ok, 1 dimension = Object, 3 expressions =Data1,Data2,Data3, right?).

                   

                  You don't need it the other way round, you need a dimension of Datatype (i.e. values Data1, Data2,Data3).

                   

                  You should use a cross table to retrieve that from your data.

                   

                  If your table is too big - well, you probably show aggregated data in the pie charts, don't you?

                   

                  Build a aggregated table, convert it to crosstable.

                   

                  Use Object and Datatype as Dimensions, activate Trelis.

                  One Expression = Data (derived by using crosstable).

                   

                  Stefan

                   

                  P.S.

                  If you have few Data columns, you might just layout the trellis on your own using separate pie chart objects.

                    • Re: Flipping Axis on Pie Chart?
                      John Witherspoon

                      If I do this:

                       

                      dimension = Object
                      expression = Data1

                       

                      Then I get a pie chart "for Data1... with a slice for each object".  Do this for each DataX, you have have what it appears you are asking for.  This seems like basic pie chart behavior, no "flipping axis" or anything, so I assume I'm misunderstanding the problem.  Is the problem that you have an unknown (ahead of time) number of DataX fields, so you can't just build one pie for each?  You need to somehow build a trellis chart for them instead?  If so, then I think Stefan is on the right track - use crosstable to convert the columns to rows, and use that new dimension in your chart.

                        • Re: Flipping Axis on Pie Chart?

                          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.

                        • Re: Flipping Axis on Pie Chart?

                          Sorry for not being "chatty" enough.   First time I have been called that!   

                           

                          I thought the table would illustrate Object as dimensions and Data1-3 as expressions, but I should not have assumed it was immediately apparent.

                           

                          I would normally use a crosstable, but the problem is that there are 20 million rows which go into it and selection criteria in other fields could limit the data and change the pie chart.   As it stands today, I am using 14GB of RAM, and would not like to make it double just for this.

                           

                          As far as aggregated table, that is out too, unfortunately, because there are a lot of other selections (as I eluded to above) that can occur which filter the data out.  

                           

                          What is interesting is there are various posts here and there about people trying to rotate the axes on various objects, it seems logical to have a simple "Flip Axes" on the result set which would enable this on any object, but then again, I haven't given it deep consideration to see what all the issues that brings up.   There are just times when it seems appropriate.

                           

                          Thanks for your input.

                            • Re: Flipping Axis on Pie Chart?
                              Stefan Wühl

                              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

                                • Re: Flipping Axis on Pie Chart?
                                  Stefan Wühl

                                  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

                                  • Re: Flipping Axis on Pie Chart?
                                    John Witherspoon

                                    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.

                          • Re: Flipping Axis on Pie Chart?

                            blackhawk,

                              This is a tough question. I didn't really notice this until I tried your example. I do not have an answer for you yet but I do have a suggestion if you are pressed for time.

                             

                            You could group the different expressions together by using the "Group" button. That way, you can flip through each of the expressions (Data1,2,3) while maintaining the Object dimension. The chart would have the Circle group button in it.  Each pie slice would be one object.

                             

                            I will let you know if I find a solution.

                            • Re: Flipping Axis on Pie Chart?

                              swuehl - That seems like an awful lot of work to come up with a pie chart. I don't like creating extra tables unless they are absolutely necessary

                               

                              Sometimes, the data just doesn't lend itself to certain types of charts. You might have a vision for your app but it just won't be possible.

                               

                              In this case, I think a bar chart with Object on the x-axis (dimension) and each "Data" item as a different colored bar would be perfect.Is it necessary to have a pie chart blackhawk?

                              • Re: Flipping Axis on Pie Chart?

                                I also like the idea to create seperate pie charts and lay out the Trellis yourself

                                • Re: Flipping Axis on Pie Chart?

                                  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?