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

    Find MAX and MIN within Dimension

    Mila T

      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
          Ombir Rathee

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

          • 2. Re: Find MAX and MIN within Dimension
            Mila T

            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
              Ombir Rathee

              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.