7 Replies Latest reply: Nov 3, 2017 9:11 AM by Cristina Punga RSS

    Qlikview bar chart

    SOUKAINA EZZENFARI

      Hello,

       

      Please tell me if its possible to display in a bar chart the pourentage i highlight in the screenshot : ( it represents the difference between the bars : example the difference between sales  and budget)

      thank you in advance

      qlikk.PNG

        • Re: Qlikview bar chart
          Cristina Punga

          Hi, can you attach the QVW file?

          Also, more precisely, in your screenshot what is that 17.8%? I mean which value did you subtract out of which value (maybe even highlight the bar names etc.)

            • Re: Qlikview bar chart
              SOUKAINA EZZENFARI

              Thank you for your answer , im sorry i can't attach my qlikview app the data is so confidentiel; i take the screenshot above from excel the final user gave us ( the values are random)

              Concerning the pourcentage it represents the difference between the bars (the values are random on the graph )

              for example for 2012 we have : 58 , 8 , 12 , 7, and for bud 2013 we have : 59, 15,13 the pourcentage represents  respectively : (59-58)/59, (15-8)/15, (13-12)/13 and so on....


              hope its clear

              thank you in advance

                • Re: Qlikview bar chart
                  Cristina Punga

                  I think I understand your question.

                  However, can you also make a screenshot of the Table viewer? I want to understand if Sales and Budget are in separate tables or as different categories in the same table etc. The screenshot would really help.

                  Thanks!

                  Cristina

                    • Re: Qlikview bar chart
                      SOUKAINA EZZENFARI

                      thank you.

                      Yes the sales and budget are in the same table, here is the screeshot of the data model: the columns used are highlighted : sales( CA_NET_EN_DH), Budget (BUDGET_CA_NET_DH) and the dimensions used are year (ANNEE) and product_type (MARQUE_PRODUIT)

                      Thank you.

                      qliksales.png

                        • Re: Qlikview bar chart
                          Cristina Punga

                          Hi again,

                          I attached a close version of what you asked, in the sense that I created a chart with the Sales, Budget and the percentage you explained earlier. However I could display the percentage as another bar in the bar chart and not as a label like in your screenshot, I hope that's ok. Also for some reason I could not stack the product types in the same bar, so I put them next to one another. Stacking them works only when I have a single expression, not 3 like I created (Sales, Budget, %)

                           

                          Let me know if the example helps. I used your values.

                          Cristina

                            • Re: Qlikview bar chart
                              SOUKAINA EZZENFARI

                              thank You so much for your reply,

                              well i think its okey for the pourcentage to be as a different bar ( i will propose it to the ffinal user) , however i need to display all the product type in the same bar? IS it impossible with the expressions i have?

                              Another question please, how do you displayed the value of the % in red under the bar??

                               

                              thank you so much

                                • Re: Qlikview bar chart
                                  Cristina Punga

                                  Hi, I calculated the percentage directly in the script because I couldn't think of any way of doing it in the bar chart directly.

                                  1) First I loaded into a table [Test] the values you showed in your screenshot, as a source table.

                                  2) Then I created separate tables for Sales and Budget out of table [Test]. The catch here was that, when I created table [Sales], for field [Year] I added 1, so when you usually had 2011 and 2012, you would in the end have 2012 and 2013 respectively (this is to help me create a key in the next step, based on Year and Product Type; since you want to match Budget of current year with Sales of previous year, I had to add 1 to the Year).

                                  3) Then I created a table [BigTable] which takes values from [Sales] and joins [Budget] by [Key] field which is a concatenation of the year and product type (join=full join, to have entire data of both tables).

                                  4) In the end I created the final table called [Table] which has everything from [BigTable] + the Year and Category (although they're not really relevant as I didn't use these last two in the chart) + field [%] which is the percentage. The expression I used to calculate % is the one you explained:

                                  (Budget-Sales of previous year)/Budget.

                                   

                                  As for displaying all products in the same bar, this is tricky because you can do this if you use only one expression, like [%] (instead of Sales, Budget and [%]). Attached is such a version of the chart. I modified some properties, like showing the bars as stacked, removing the text on axis, putting only [%] expression, and putting dimensions Category and Year, instead of [Key] that I used in the first version of the qvw.

                                   

                                  Hope this helped.

                                  Cristina