nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Growth percentage in bar chart

piramanayagam.td

Hi,
 
I have a time dimensional sales data for 3 years. I am able to display 3 years sales in a bar chart. I also need to display the growth percentage in secondary axis, but I am not sure how to calculate this value, because I need to calculate using the values in the rows rather columns. My data structure is like,
 
Year|Sales 
13-14|100 
14-15|210 
15-16|111
 
My growth formula is (currentrowvalue - previousrowvalue)/previousrowvalue and calculation should start from 2nd row only.
 
FYI. I do not have the growth percentage as a value in the database, I need to calculate it on the fly.
 
Kindly help.

Find more posts tagged with

Comments

pricher

Hi,
 
You can calculate the year over year growth as a computed column in your data set.
 
In the attached example, the initial data set calculates the sum of quantity ordered by month. 
<pre class="_prettyXprint _lang-">
select sum(od.quantityordered) as qty
, month(o.orderdate) as yr
from orders o
, orderdetails od
where o.ordernumber = od.ordernumber
group by month(o.orderdate)
</pre>
To calculate the growth, first create a report variable called keepQty to store the quantity of the previous row:

Migrateduser

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144520" data-time="1467729617">
<div>
Hi,
 
You can calculate the year over year growth as a computed column in your data set.
 
In the attached example, the initial data set calculates the sum of quantity ordered by month. 
<pre class="_prettyXprint _lang-">
select sum(od.quantityordered) as qty
, month(o.orderdate) as yr
from orders o
, orderdetails od
where o.ordernumber = od.ordernumber
group by month(o.orderdate)
</pre>
To calculate the growth, first create a report variable called keepQty to store the quantity of the previous row:
 
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13439'>Snap 2016-07-05 at 10.33.24.png</a>
 
Then, in the data set, add a new Computed Column "growth" with the following expression:
 
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13440'>Snap 2016-07-05 at 10.32.50.png</a>
 
This will create a 3rd column in the data with the year over year growth:
 
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13441'>Snap 2016-07-05 at 10.33.54.png</a>
 
You can then use the 3rd column in the secondary axis of the chart.
 
Hope this helps,
 
P.
</div>
</blockquote>
Hi Pierre
 
I have a similar requirement where I have to caluclate the growth and show it in chart. I cant use Computed column as you mentioned in the above example since I'm using data model in my report.
 
Is there any way other than data set computed column to achieve the same, it would be of great help if you could explain with the help of data model example.

pricher

Hi,
 
This new sample report uses a different approach which could also be applied to the previous requirement. The beauty of it is that it doesn't require coding.
 
It is based on a simple data model that returns 4 columns, of which I will use two: ORDERDATE and TOTAL

Migrateduser

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144685" data-time="1469115610">
<div>
Hi,
 
This new sample report uses a different approach which could also be applied to the previous requirement. The beauty of it is that it doesn't require coding.
 
It is based on a simple data model that returns 4 columns, of which I will use two: ORDERDATE and TOTAL
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13483'>Snap 2016-07-21 at 11.20.46.png</a>
 
Drop a crosstab component in the report and use ORDERDATE as the grouping field and TOTAL as the measure to display:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13484'>Snap 2016-07-21 at 11.22.37.png</a>
 
Add a Relative Time Period measure by clicking on the icon to the right of the measure:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13485'>Snap 2016-07-21 at 11.24.02.png</a>
 
Create the Relative Time Period measure to capture the total value of the preceding year:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13486'>Snap 2016-07-21 at 11.26.08.png</a>
 
Add a Derived Measure to calculate the growth between This year and Last year:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13487'>Snap 2016-07-21 at 11.27.46.png</a>
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13488'>Snap 2016-07-21 at 11.29.53.png</a>
 
Right-click on the crosstab and choose Create Chart View
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13489'>Snap 2016-07-21 at 11.31.00.png</a>
 
In Select Chart Type, choose Bar, Multiple Y Axis and Line Series:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13490'>Snap 2016-07-21 at 11.32.51.png</a>
 
In Select Data, choose the 2 series:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13491'>Snap 2016-07-21 at 11.34.14.png</a>
 
In Formart Chart, you can format the way the growth percentage is displayed:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13492'>Snap 2016-07-21 at 11.36.10.png</a>
 
I have attached the report and the data design.
 
Hope this helps,
 
P.
</div>
</blockquote>
Thanks a ton Pierre.
BTW did you attach the design, I couldn't see any.

pricher

Report and data design now attached to the thread.
 
P.