5 Replies Latest reply: Sep 20, 2016 4:13 AM by Sebastian Fagerlund RSS

    Sum Aggr()

    Sebastian Fagerlund

      Why dosen't this works? :/

       

      Sum(aggr( DISTINCT sum({<Attribute = {'M1'}>}ConsumptionPerDay*5) * RecipeShare, ProductID))

       

      It dosen't sum if it's several ProductIDs. It works if there are only one ProductID.

       

      I have also tried:

      sum(aggr( sum($(vVariable)),ProductID))

       

      Where $(vVariable) = sum({<Attribute = {'M1'}>}ConsumptionPerDay*5) * RecipeShare

        • Re: Sum Aggr()
          Sunny Talwar

          What is the dimension where you are doing this? Can you may be share a sample to see what might not be working in this.

          • Re: Sum Aggr()
            Stefan Wühl

            I assume RecipeShare does not show a unique, single value per ProductID.

             

            It's equivalent to write your expression as

             

            Sum(aggr( DISTINCT sum({<Attribute = {'M1'}>}ConsumptionPerDay*5) * Only(RecipeShare), ProductID))

             

            Use Aggregation Functions!

             

            Not sure what you intend to calculate, but either apply an aggregation function other than Only() to RecipeShare, or maybe add RecipeShare to the Sum():

             

            =Sum(aggr( DISTINCT sum({<Attribute = {'M1'}>} ConsumptionPerDay*5 * RecipeShare), ProductID))


            Or maybe you need to add more dimensions to the aggr() function to allow Only(RecipeShare) to work.

              • Re: Sum Aggr()
                Sebastian Fagerlund

                Hi,

                I think i need one more dimension. The RecipeShare is a unique value per ProductID and raw material.

                Example:

                ProductID     RawMaterialID     RecipeShare     Forecast

                5001             28025                    0,2                       10

                5001             27051                    0,8                       10

                 

                4025              28025                    0,3                      7

                4025              23047                    0,5                      7

                4025              25045                    0,2                      7

                 

                And diffrent ProductID shares the same RawMaterialID. So if i choose raw material 28025 i want to see on a row how much RawMaterial  total i need based on the forecast for the ProductID.

                 

                Correct answer for 28025 would be 6,1 (0,2*10+0,3*7).