52 Replies Latest reply: May 23, 2017 2:13 AM by Supriya R RSS

    How to create multiple text values on Bar Chart

      I keep getting asked this question and have been unable to find an acceptable working solution.

       

      We have a bar chart that has two dimensions (entity and rating). We have one expression for the count.  The bar charts are stacked. I've attached a picture for reference.

       

      The question we keep getting asked is can we have the percentage relative to one of the ratings along with the total number. In other words, if entity "ABC" has 100 Pass ratings and 5 Marginal Rating and 10 Fail Ratings, the user would like to see the total count and the percentage of fails next to each bar on the chart. So the display for this entity would show for example "115 / 8.70%".

       

      Obviously, we can create tables of numbers that show this information, but the users want the visual appeal of the chart with the number right there and not have to look around or try to associate line 1 with line 1 from a text grid, etc...

        • Re: How to create multiple text values on Bar Chart
          Rebecca Molstad

          This isn't exactly what you are looking for, but it may help in the meantime. Put the formula for failure percentage as an expression and select 'Invisible'. Select 'Text as Pop-up'. The percentage will show when you hover over the selection. Here is an example.

          • Re: How to create multiple text values on Bar Chart
            whiteline _

            Hi

            You can use dual() function for that.

            For numeric part use your count expression and for text part - construct the desired string.

            • Re: How to create multiple text values on Bar Chart
              Curt Daughtry

              In addition to the other ideas mentioned, a simple way is to just concatenate manually the expression to receive the percentage after the expression used for the count. So something like:

               

              =count(field)&' / '&(sum(field/field)*100)&'%'

                • Re: How to create multiple text values on Bar Chart

                  Have you tested this solution to make sure that it will actually work?

                   

                  ==================================================

                  In addition to the other ideas mentioned, a simple way is to just concatenate manually the expression to receive the percentage after the expression used for the count. So something like:

                   

                  =count(field)&' / '&(sum(field/field)*100)&'%'

                    • Re: How to create multiple text values on Bar Chart
                      Curt Daughtry

                      Yes, I did.

                       

                      Have you?

                        • Re: How to create multiple text values on Bar Chart

                          I modified my expression in my chart very simpy by adding a string to the end of it similar to what you propose...

                           

                          My current expression is:

                           

                          Sum ([Sample Count])

                           

                          I modified it to simply be:

                           

                          Sum ([Sample Count]) & 'XXXX'

                           

                          And the result is my chart no longer display any data. I get the "No data to display", so I'm not sure how you made a stackable bar chart work with that expression you posted.

                           

                            • Re: How to create multiple text values on Bar Chart
                              whiteline _

                              Sum ([Sample Count]) & 'XXXX'  << its a string

                              You can't plot string value on numeric axis

                              • Re: How to create multiple text values on Bar Chart
                                Curt Daughtry

                                Okay, I did a demo test and ran into the same problem....so without directly addressing the issue for the stacked bar chart - I found a working way to achieve the same thing (with the stacked bar chart).

                                 

                                -Create the same expression your trying to use to get the stacked bar so in this case sum([Sample Count]) & 'XXXX'

                                -make the expression invisible

                                -select "Text on Axis"

                                 

                                This should allow your stacked bar to appear as it did before while adding your (multiple) data values pertaining to the bar along the axis.

                                 

                                Got to say that I'm not totally satisfied with the design if you have a long value to display but it works and has potential to be formatted to a more polished presentation. Either way, sorry for the confusion.....the point I was driving at was that while you try to figure out the more complex methods that generate the solution you need, you can serve up a working solution to the end-user with a "work-around", if you will, that achieves the same effect by using a simplier method. Learned this from an example where it was requested to display the customer's id next to the customer dimension, possibly in rollover pop-up or as another dimension. This was going to cause either a great burden on maintenance (popup) or exceed the working display area that I had to present the chart....so I just put the customer's id as a tag along to the dimension i.e. Customer&' - ('&CustomerId&')'

                                 

                                Anyway, hope that helps.

                            • Re: How to create multiple text values on Bar Chart
                              whiteline _

                              Hi, bneumeier.

                               

                              Sorry, I've tested dual() expression with bar chart and it works in many cases except stacked mode

                               

                              You can easily test it yourself:

                              =dual('test ' & Count(YourValue), Count(YourValue))

                               

                              But you can use 'text on axis' instead 'values on data points' if its suitable.

                                • Re: How to create multiple text values on Bar Chart

                                  So to be clear - if I use dual in the expression and turn the option for "Text on Axis" on the stacked bars will appear correctly, the text will just be outside the chart on the axis - correct?

                                   

                                  If that is correct, it might be an option, they will not like it, but we might be able to sell it as am option for a little while.

                                    • Re: How to create multiple text values on Bar Chart

                                      I am reporting the problem with Dual as a bug, but unsure what kind of response I will receive

                                        • Re: How to create multiple text values on Bar Chart
                                          whiteline _

                                          yeah, you right about axis text. Also its grayed. And you have to play with formats to show what you want.

                                          Anyway, it will hepl the users to see what they want.

                                           

                                          I think it's a complex question whether it a bug or 'inimplementation' or feature.

                                           

                                          Consider that in stacked mode QV shows Total value for a stack, while when you type the expression you do it for the entire dimension (don't know how to say it more clear).

                                           

                                          Anyway, I hope it helps you.

                                            • Re: How to create multiple text values on Bar Chart

                                              I changed the expression to utilize Dual and then changed to "Text on Axis". Still did not get the text, instead received individual counts for each dimension. This will not work. They want to see the total count and the percentage for a specific dimension relative to the count and not have to calculate it by hand. They want this text to be on/related to each bar.

                                                • Re: How to create multiple text values on Bar Chart
                                                  whiteline _

                                                  2  bneumeier:

                                                   

                                                  You're right. It adds something strange 'on axis' in stacked mode. Sometimes values from one level and somtimes from other.

                                                  I've managet to achive the same results as with "Text on Axis" but with 'Values on data points' (see the above post about adding invisible expression).

                                                   

                                                  The problem is that you can't calculate it through every value of the 'stack'. Sometimes it takes one value from top and some times another from bottom. So unfortunately you can't calculate the ratio and the value of each part as you want.

                                                  I think this is another reason why QV overrides text representation of expression by default and displays only total amount for a stack bar.

                                                    • Re: How to create multiple text values on Bar Chart
                                                      Curt Daughtry

                                                      I think I'm missing something.....

                                                       

                                                      When you create the invisible expression to appear as "Text on Axis", I am under the impression that you are calculating the provided expression expression against the dimension and displaying the resulting value along the axis.

                                                       

                                                      Is this not the case?

                                                        • Re: How to create multiple text values on Bar Chart
                                                          whiteline _

                                                          can we have the percentage relative to one of the ratings along with the total number

                                                          2 autopilot:

                                                           

                                                          You calculate it against one (bar) dimension while the question is how to display the ratio of each stacked part (second dimension).

                                                           

                                                          Your sample is oversimplified, create a stacked bar chart (2 dims), calculate the real sum value (don't substitute it to zero).

                                                          Then play with that chart, try to get  sum values in text (for each part of stack and entire for the stack).

                                                           

                                                          Additional invisible expression works in both cases whether "Text on Axis" or 'Values on data points'. And works bad.

                                                        • Re: How to create multiple text values on Bar Chart

                                                          Interestingly enough they don't technically override it. If you go to the Presentation tab there is an option in the middle of the screen called "Plot Values Inside Segments". If you select that option the individual bars get their numbers if there is room to display them. So to me it appears the numbers are there, just need a way to get at them and display them in a custom format.

                                                           

                                                           

                                                          ============================================

                                                          The problem is that you can't calculate it through every value of the 'stack'. Sometimes it takes one value from top and some times another from bottom. So unfortunately you can't calculate the ratio and the value of each part as you want.

                                                          I think this is another reason why QV overrides text representation of expression by default and displays only total amount for a stack bar.

                                                            • Re: How to create multiple text values on Bar Chart
                                                              whiteline _

                                                              It seems to work... (I have 2 dims: d1 and d2, and Plot Values inside segments checked)

                                                              =dual(Sum(v) & ' (' & num(Sum(v)/Sum(total<d1> v), '0%') & ')', Sum (v))

                                                                • Re: How to create multiple text values on Bar Chart

                                                                  Tried that and it did not work. Here is the expression I used:

                                                                   

                                                                  dual(Sum([Sample Count]) & ' (' & num(Sum([Sample Count])/Sum(total<Rating> [Sample Count]), '0%') & ')', Sum ([Sample Count]))

                                                                   

                                                                  And a screen shot

                                                                  EntityChart example2.PNG

                                                                    • Re: How to create multiple text values on Bar Chart

                                                                      And for information my dimension 1 is a cyclic group, dimension 2 is Rating.

                                                                        • Re: How to create multiple text values on Bar Chart
                                                                          whiteline _

                                                                          Change the number format of expression to Mixed.

                                                                           

                                                                          That may be a problem:

                                                                          Sum(total<Rating> [Sample Count])

                                                                           

                                                                          Shouldn't you calculate total for the first dimension (I thought you want to calculate the ratio of each part of row/stack) ?

                                                                          If so, I don't know whether total<CyclicGroupLable>  works.

                                                                          If it doesn't work you can simply write separate expressions for each field in cyclic group and then use GetCurrentField(groupname) function with if statement to choose the right one.

                                                                            • Re: How to create multiple text values on Bar Chart

                                                                              I do not see an option for mixed, or I'm not sure where that option exists.  See screen shot attached

                                                                               

                                                                              Additionally, they want to see the total count for the line and the percentage for one specific rating which in this case happens to be the ones with Fail.

                                                                               

                                                                              Additionally, when the numbers are displayed on the individual segments, the system will not display numbers where the segments are to small. So having the percentage displayed on each is a good idea, but the system is not going to display that when the percentage is say 1% because it can not fit.

                                                                               

                                                                               

                                                                              Screenshot:

                                                                              EntityChart example3.PNG

                                                                                • Re: How to create multiple text values on Bar Chart
                                                                                  whiteline _

                                                                                  Choose Expression Default.

                                                                                  Otherwise QV replaces text representation of dual() function with selected format.

                                                                                  • Re: How to create multiple text values on Bar Chart
                                                                                    whiteline _

                                                                                    So having the percentage displayed on each is a good idea, but the system is not going to display that when the percentage is say 1% because it can not fit.

                                                                                    In this case, is it important compared to other segments ?

                                                                                    I guess, to analyse such small deviations user should select something,  otherwise it doesn't make sense.

                                                                                      • Re: How to create multiple text values on Bar Chart

                                                                                        Moderate progress made. Obviously, this does not work for my scenario where they want to see to total for the entire line and the percentage for the fail rating, but I am posting this so others who may be looking/browsing see that they can do this one option.

                                                                                         

                                                                                        Expression is:

                                                                                         

                                                                                        dual(Sum([Sample Count]) & ' (' & num(Sum([Sample Count])/Sum(total<[Supplier Group 1]> [Sample Count]), '0%') & ')', Sum ([Sample Count]

                                                                                        ))

                                                                                         

                                                                                        Dimension 1 is a cyclic group called "Supplier Group 1"

                                                                                         

                                                                                        And here is the result. Good to know we can at least do this. Options needed to make work are on the Expressions tab "Values on Data Points", on the Presentation tab "Plot Values Inside Segments" and on the Number tab "Expression Default"

                                                                                        EntityChart example4.PNG

                                                                                         

                                                                                        Good to know we can do this. Thanks for the assistance and insight even though it doesn't meet my specific requirements. If they would just allow this to dual function to work for the entire line where values are displayed at end on line and not the segments, I could make it work. So close.

                                                                                          • Re: How to create multiple text values on Bar Chart

                                                                                            And now I'm thinking I can get there by putting total in the first sum that give me my total now I just need to figure out how to get the expression right for getting the Fail ratings. Thoughts?

                                                                                             

                                                                                             

                                                                                            dual(Sum(total<[Supplier Group 1]> [Sample Count]) & ' (' & num(Sum({<Rating = {"Fail"}>} [Sample Count])/Sum(total<[Supplier Group 1]> [Sample Count]), '0.00%') & ')', Sum ([Sample Count]

                                                                                            ))

                                                                          • Re: How to create multiple text values on Bar Chart

                                                                            A bit like receccad suggested, I do aging bar charts with percentages appearing at the bottom and values along the top of the bars, using two expressions, one relative and one absolute. By setting the two  expressiong to have different axis, the bar distance to 0 and the bar clustering to 8 it just about passes (see attached).

                                                                            Can override the grey text by changing the Text Colour behind expression that is on the axis.

                                                                            Jonathan

                                                                              • Re: How to create multiple text values on Bar Chart

                                                                                Thanks for your example, but you are not using a stacked bar chart and thus I do not believe what you are doing will work for us.

                                                                                 

                                                                                jonbroughavone wrote:

                                                                                 

                                                                                A bit like receccad suggested, I do aging bar charts with percentages appearing at the bottom and values along the top of the bars, using two expressions, one relative and one absolute. By setting the two  expressiong to have different axis, the bar distance to 0 and the bar clustering to 8 it just about passes (see attached).

                                                                                Can override the grey text by changing the Text Colour behind expression that is on the axis.

                                                                                Jonathan