Organic vs Inorganic growth - % of change in tibco spotfire


First, I need to explain the title. Organic growth is sales growth from same point of sales that exists on previous year. so Without any opening, and the inorganic is the one from new points of sales. 


Now, I have sales of several articles, raw database and I need to group it by point of sales (POS), and calculate this organic growth.  For example POS31 open on June 2015.  so to calculate total growth on june, july... 2015 I doest not add the value of sales.  but when I want to calculate the % of grouth for june 2016 (1 year of data) I should make the calculation with values from 2015 with this POS).  

Using a Cross Table, I can Hide sales if there is no sales o previous year, using an expression like: 

if(Sum([Sales]) OVER (ParallelPeriod([Axis.Columns]))>0,sum([Sales),0)  (basically if the sales on previous year exists and is greater than zero, then use it, if not, doesn´t)

This using date (year/Month) on horizontal axis and POS on Vertical axis. 


But this calculate MISS when I try so get the column total sales, since add the sales from items already "hidden". 


So in the same way, I need to hide those values on the total. 

(this is the issue 1) 


As issue 2. 


I usually use the expression: 

Sum([Sales]) / Sum([Sales]) OVER (ParallelPeriod([Axis.Columns]))) - 1) * 100 as [Var % Same Month year Before]


and I need to modify it, in order to use values from the first sales. 


I am attaching sample data. Unfortunatelly, there is not too much data, onli 2014,2015 and 2016, but the structure should be good for up to 10 years. 






Binary Data organic-inorganic_grow.dxp1.05 MB

(1) Answer