3 Replies Latest reply on Oct 19, 2018 2:55 AM by Ombir Rathee

# Find MAX and MIN within Dimension

Hello guys,

I have Calculated field (Dimension) with range -1,0,1,2,3,4,5,6,7,8,9 and this range is not fix.

Now, I need calculation to find MIN value here ---> which is -1 and to find MAX value ---> which is 9, currently, and for MAX/MIN data to use Sales value and finally to say MAX-MIN.

Any idea how to achieve this?

Thanks!

• ###### 1. Re: Find MAX and MIN within Dimension

What are you getting with :  Max(Field Name)- Min(Field Name) ?

• ###### 2. Re: Find MAX and MIN within Dimension

It's not that I need.

I'll try to clarify it.

I have forecast data for each month starting from December 2017. The name for December's forecast is "-1" and so on and so forth,  btw this is calculated field in order to find easier MIN  forecast (which is December 2017 as "-1") and MAX - lataest forecast, which is (Septmber 2018 as "9") now. I need Calculated field which will subtract latest forecast from first.

So, next time when I refresh data and get October 2018 forecast, I need October 2018 - December 2017 ("10" - "-1").

Do you understand me a little bit more now :-D?

Thanks.

• ###### 3. Re: Find MAX and MIN within Dimension

Create two Calculated fields:

Max Data:

IIF([Date Field]={MAX([Date Field])},[Forecast Data],0)

Min Data:

IIF([Date Field]={MIN([Date Field])},[Forecast Data],0)

and then create another calculated field:

Diff:

Max Data - Min Data

If this doesn't solve your problem then please attach a sample workbook with expected output.