8 Replies Latest reply: Oct 26, 2015 12:38 PM by Stefan Wühl RSS

    Aggr function with rangemax

      Hi,

       

       

      I have the following table:

       

       

      Storage facilityMaterial IDMaterial needs last yearMaterial needs this yearEstimated needs ( =Max(this year, last year)
      1xx100120120
      1xy80080
      1xz507070
      2xx80080
      2xy503050
      2xz402040

       

      Total needs by material ID:

       

      xx - 200

      xy - 130

      xz - 110

       

      I want to create a new table looking at the aggregate needs only


      TABLE 2

       

      Material IDEstimated needs ( =Max(this year, last year)
      xx200
      xy130
      xz110

       

       

      I am using this function:

       

      =sum(aggr(Max(Rangemax(Material needs this year,Material needs last year)), Material ID, Storage Facility))

       

       

      My problem is that the function yields these values as max value:

       

      torage facilityMaterial IDMaterial needs last yearMaterial needs this yearEstimated needs ( =Max(this year, last year)
      1xx100120120
      1xy8000
      1xz507070
      2xx8000
      2xy503050
      2xz402040

       

       

      Thus the aggregation does not result in the desired outcome.

       

       

      I have tried to use an IF formula instead, but the problem is related to the AGGR part of the function.

       

      Any ideas?

        • Re: Aggr function with rangemax
          Sunny Talwar

          Which column are you using the expression on??? Can you post a sample qvw file to see the issue you are facing?

            • Re: Aggr function with rangemax

              Hi Sunny T,

               

              I apologize that my description of the issue lacks details. I had to put it together fast before I went home from work.

               

              Here are some more details:

               

              Sample of source data:

              • Assume there are more months than month 1 and 2, but this is a sample
              • In my dashboard I choose what month to look at

               

              Storage facilityMaterial IDMonthMaterial needs last yearMaterial needs this year
              1xx1120-
              1xy1800
              1xz150100
              1xx230-
              1xy24050
              1xz26030
              2xx180-
              2xy15070
              2xz14020
              2xx250-
              2xy24030
              2xz26020

               

               

              One of my tables should show me the the following:

              • Dim: Storeage facility, Material ID
              • Fact: Estimated material needs (Max of material needs this year vs last year)

               

              Thus I choose to look at month 1 and 2 combined

               

              Storage facilityMaterial IDEstimated material needs ( =Max(this year, last year)
              1xx150 (from material needs last year)
              1xy120 (from material needs last year)
              1xz130 (from material needs this year)
              2xx130 (from material needs last year)
              2xy100 (from material needs this year)
              2xz100 (from material needs last year)

               

              To calculate the estimated needs, it seems to work when I calculate the sums of last and this year given the selection of month 1 and 2, and use an IF function to pick the largest amount.

               

              The Twist:

               

              In my second table I want to drop the Storage facility dimension. and I want the sum of estimated needs by material ID to yield the same total as if i summarized by material ID in the table above:

               

              Desired result:

              Material IDEstimated needs ( =Max(this year, last year)
              xx280
              xy220
              xz230

               

              I tried to use this function:

              =aggr(Max(Rangemax(Material needs this year,Material needs last year)), Material ID, Storage Facility))

               

              However, this did not get me there so I checked what the aggr-function actually calculate. This is what I got when I included all the dimensions AND added the aggr-function as an expression:



              Storage facilityMaterial IDEstimated needs when using the IF formulaEstimated needs (aggr-function)
              1xx150 (from material needs last year)-
              1xy120 (from material needs last year)120
              1xz130 (from material needs this year)130
              2xx130 (from material needs last year)-
              2xy100 (from material needs this year)100
              2xz100 (from material needs last year)
              100



              The Challenge:

               

              It seems that when I have blank cells for needs this year, the aggr function does not work properly.

              • My hypotheses is that I need a way to make the Aggr-function reed the blanks as 0 values.
              • This does not seem to be soleved by adding an IF-function saying that if the value is blank then use 0

               

              Any ideas?

                • Re: Aggr function with rangemax
                  Stefan Wühl

                  You just need to aggregate the materials need across all months per your aggr() dimensions:

                   

                  =Sum(

                      aggr(

                          Rangemax(Sum([Material needs this year]),Sum([Material needs last year])),

                    [Material ID],[Storage facility])

                  )

                    • Re: Aggr function with rangemax

                      Hi,

                       

                       

                      Thanks  for the reply.

                       

                      I have tried the function but it does not seem to work properly.

                       

                      Thus, I did some research on where the error might lie.

                       

                      It seems the aggregation function does not interpret my expression correctly. Look at the picture and see that I have tried to replicate a column using an Aggr-function. 'I did this only to investigate what actually came out of the aggr-function, thus I included all dimensions I aggregated on. IMG_1015.JPG

                       

                      It yields the correct result only in some occations.

                       

                      I thought the Aggr - function could be used to create a "hidden" table and that i should get the same results?

                • Re: Aggr function with rangemax
                  Miguel Braga

                  See the attached file.

                  • Re: Aggr function with rangemax
                    Patrick Roser

                    You could just use a straight Table,

                     

                    Material ID as Dimension, and sum(if(thisYear > lastYear, thisYear, lasYear)) as  expression.

                     

                    another approach would be the expression you mentioned extended with a total for the Mat ID:

                    =sum(TOTAL <Material ID> aggr(Max(Rangemax(Material needs this year,Material needs last year)), Material ID, Storage Facility))


                    Patrick

                    • Re: Aggr function with rangemax
                      Massimo Grossi

                      sum(aggr(RangeMax([Material needs last year],[Material needs this year]), [Storage facility], [Material ID]))

                       

                      1.png