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)
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.
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)
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.
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??
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.