3 Replies Latest reply on Feb 13, 2017 9:46 AM by Jason Bernstein

# Year to Year Inflation of Common Products Across Year

I would like to change my current methodology on trend calculations.  Currently, the methodology is as written:

( SUM( IIF( YEAR( [Date of Fill] ) = 2016  ,  [Cost]  ,  0 ) )

/

SUM( IIF( YEAR( [Date of Fill] ) = 2016  ,  [Quantity]  ,  0 ) ) )

/

( SUM( IIF( YEAR( [Date of Fill] ) = 2015  ,  [Cost]  ,  0 ) )

/

SUM( IIF( YEAR( [Date of Fill] ) = 2015  ,  [Quantity]  ,  0 ) ) ) - 1

This is not however how many would define "Inflation."  This is more of an overall cost increase.

What I need to do instead is find the products that existed in both years, 2016 and 2015, and calculate the inflation on ONLY those products.

In code that would look something like this:

( SUM( IIF( YEAR( [Date of Fill] ) = 2016 AND <product exists in 2015>  ,  [Cost]  ,  0 ) )

/

SUM( IIF( YEAR( [Date of Fill] ) = 2016 AND <product exists in 2015>  ,  [Quantity]  ,  0 ) ) )

/

( SUM( IIF( YEAR( [Date of Fill] ) = 2015 AND <product exists in 2016>  ,  [Cost]  ,  0 ) )

/

SUM( IIF( YEAR( [Date of Fill] ) = 2015 AND <product exists in 2016>  ,  [Quantity]  ,  0 ) ) )  -  1

Any solution would be greatly appreciated, thanks!

• ###### 1. Re: Year to Year Inflation of Common Products Across Year

Hi Jason

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Year to Year Inflation of Common Products Across Year

Maybe this

zn(( SUM( IIF( YEAR( [Date of Fill] ) = 2016 and  {[vCost]}=0,  [Cost]  ,  0 ) )

/

SUM( IIF( YEAR( [Date of Fill] ) = 2016 and {[vQuantity]}=0 ,  [Quantity]  ,  0 ) ) )

/

( SUM( IIF( YEAR( [Date of Fill] ) = 2015 and  {[vCost]}=0,  [Cost]  ,  0 ) )

/

SUM( IIF( YEAR( [Date of Fill] ) = 2015 and {[vQuantity]}=0 ,  [Quantity]  ,  0 ) ) ) - 1)

create variables vCost=zn(SUM([Cost])) and vQuantity=zn(SUM([Quantity]))

The cost and quantity will be zero if there are no values.

HTH,

Peter

PS: A sample data will be more helpful.

2 of 2 people found this helpful
• ###### 3. Re: Year to Year Inflation of Common Products Across Year

Attached sample

Basically what I am looking for is to automatically exclude products B223 and E99 because these products have not been sold in both 2015 and 2016, and thus do not reflect true "Inflation"

So, if there is any way to set an automatic filter to include only products that exist in the data in both years, that would work.  Or if it can somehow be incorporated into the code of inflation.

Thank you