5 Replies Latest reply on Aug 25, 2016 9:56 AM by Andrew Watson

# Calculate the difference between min and maximum values

I have a situation. I want to calculate the difference between min value of price and maximum value of price for each product id for each month. Price is just one column. I have duplicated price measure and calculated min and max price from the duplicates. But i am not able to figure out how can i calculate the difference between min and max price for each product id for each month (Context: The price of a product id varies during a month)

• ###### 1. Re: Calculate the difference between min and maximum values

Hi Prasenjeet Acharjee,

First make a calculated field

max([sales]) - min ([sales]) and name it sales difference and you will get the output you want.

Regards,

Vaishali Tanwar

1 of 1 people found this helpful
• ###### 2. Re: Calculate the difference between min and maximum values

LOD calcs should help here. Try this to get the max date:

{FIXED [Product No],MONTH([Inv Date]):MAX([Price])}

This to get the min date: {FIXED [Product No], MONTH([Inv Date]):MIN([Min Price])}

I you're using v9.x the MONTH([Inv Date ]) will need to be made into it's own calculated field.

To finish off and calculate the difference join these fields into a new calculated field:

MAX([Max Date Field]) - MIN([Min Date Field])

Note this hasn't been tested as you didn't provide any data but should push you in the right direction at least.

1 of 1 people found this helpful
• ###### 3. Re: Calculate the difference between min and maximum values

Hi Vaishali,

Your approach wont work in my case as I have already tried it. Thanks.

• ###### 4. Re: Calculate the difference between min and maximum values

Hi Andrew,

I tried your approach. I did not work for me. maybe i am not doing it the right way. I am attaching the workbook for your reference. Please attach the workbook if you find a solution to my problem.

For better understanding:- The product price varies during a month many times.I want to calculate the difference between price variation during a month for each product id.

If possible, please explain your solution as well so that I can learn from the solution. Thanks you for your effort and patience.

• ###### 5. Re: Calculate the difference between min and maximum values

What I described does work. Also what Vaishali suggested does work and is probably the better solution, depending on how you intend to use this data, as it avoids the LoD calc.

What makes you think it doesn't work? How are you using the result of the monthly difference?