topic Re: Qlikview bar chart in QlikView

Qlikview bar chart

Anonymous — Wed, 01 Nov 2017 15:05:19 GMT

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

Re: Qlikview bar chart

cristinapo — Wed, 01 Nov 2017 16:08:10 GMT

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

Anonymous — Wed, 01 Nov 2017 16:50:39 GMT

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

cristinapo — Thu, 02 Nov 2017 08:38:21 GMT

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

Anonymous — Thu, 02 Nov 2017 09:47:50 GMT

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.

Re: Qlikview bar chart

cristinapo — Thu, 02 Nov 2017 15:19:56 GMT

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

Anonymous — Fri, 03 Nov 2017 10:11:00 GMT

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

cristinapo — Fri, 03 Nov 2017 13:04:53 GMT

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