4 Replies Latest reply on Jun 20, 2016 2:46 AM by Sunil Pandey

# denominator value changing for different time periods

Hi All,

I have been facing this challenge where in the denominator value remains constant for a particular time period, however should change when the time period changes.

 Date Product Price Index1 Index1 05.12.15 HEALTHIER BISCUITS 2 =2/2 =2/5 05.12.15 FIBRE ONE 5 =5/2 =5/5 05.12.15 Chocolate Fudge Brownies 4 =4/2 =4/5 02.01.16 HEALTHIER BISCUITS 6 =6/6 =6/4 02.01.16 FIBRE ONE 4 =4/6 =4/4 02.01.16 Chocolate Fudge Brownies 8 =8/6 =8/4 30.01.16 HEALTHIER BISCUITS 5 =5/5 =5/6 30.01.16 FIBRE ONE 6 =6/5 =6/6 30.01.16 Chocolate Fudge Brownies 3 =3/5 =3/3

In the above example, I want to calculate Index1 and Index2.

Formula for Index1= Price of the product/Price of the Base Product (HEALTHIER BISCUITS)

The price of the base product  will change as the time period changes

Formula for Index2 :  similar to Index1, however only the base product changes from  "HEALTHIER BISCUITS"  to "FIBRE ONE"

Thanks!

• ###### 1. Re: denominator value changing for different time periods

Hi
I attached an example for Index1

You should use Level of details to calculate the denominator for all rows partitioned by the Date
For Index1 I calculated the denominator in such way:
{EXCLUDE [Product] : sum( IIF([Product] = 'HEALTHIER BISCUITS',[Price],NULL) ) }

For Index2 you should simply change the name of you base product

1 of 1 people found this helpful
• ###### 2. Re: denominator value changing for different time periods

You can also get the same without using LOD from table calculation. See the attached workbook (version 9.2).

Mahfooj

• ###### 3. Re: denominator value changing for different time periods

Thank you Vasily for your help.

• ###### 4. Re: denominator value changing for different time periods

Thank you Mahfooj for showing me another way of getting the desired output.

Much appreciated.

Thanks again!