3 Replies Latest reply: Mar 8, 2012 11:12 AM by Stefan Wühl RSS

    Aggr (I think) Help

      I have a table which lists these columns:

      Company

      Chain

      Brand

      Item

       

      Another table has these:

      Company

      Item

      On Hand Qty

       

      They two tables are joined on a combination of Company and Item.  The same item can be in many chains in the first table, but will only show up once per company in the second.  I want to sum the On Hand Qty by Brand.  When I try the expression below I get incorrect figures.  The numbers are far too low and some brands are missing entirely. 

       

      SUM(AGGR([On Hand Qty]), [Brand])

       

      Please help!  I'm not sure I completely get the AGGR function.  Thanks!

        • Aggr (I think) Help
          Stefan Wühl

          Not sure if I fully got it, have you actually JOINed the two tables? And you want to use the above expression in a table object with what dimensions?

           

          I assume you may need something along these lines:

           

          =SUM(AGGR ( SUM([On Hand Qty]), [Brand], [Item]))

           

          but maybe I just don't understand your setting. A small sample app might help, too.

           

          Regards,

          Stefan

            • Aggr (I think) Help

              Thanks for the quick response.  Sorry, I mispoke.  They are not joined, not sure of the proper term, but they are distinct tables keyed on the fields I mentioned.  I did try your suggestion though and I get the missing brands but now the figures are way over.  I think it is duplicating data.  So, if the item on hand for a company is 10 and the item is in 10 chains the on hand is showing as 100. 

               

              If I can't get this figured out I will post a sample, but there is so much in this document I fear I will spend hours just doing that, which may be what is necessary in the end.  Thanks again!

                • Aggr (I think) Help
                  Stefan Wühl

                  Maybe you don't need to build a full sample, some lines of INLINE table data might be sufficient, that demonstrate how the relation ship between the fields is. For example, is it possible that the same item belongs to different brands? And what about brands and chains?