3 Replies Latest reply: Nov 9, 2012 4:29 PM by M Paeper RSS

    Duplicating aggregation total across all dimensions or calculating portion of total Aggr across all dimensions?

    M Paeper

      Hi,

       

      Spent a few hours on this and not coming right so probably missing something fundamental. Please can somone help me resolve.

       

      I have also tried doing this in scripting using Sum() and GROUP BY ColourMix, SizeI clauses. Also loading the same physicalAvailable value multiple times by giving it different LOADed names, and writing expressions to Aggr just that name for that Size, but nothing has worked out for me.

       

      At present I have an output table like this. Its really just an inventory table with an itemId in a particular ColourMix located in a particular warehouse displayed as the inventory quantity per Size.

       

      I then want to calculate how much of a particular size is of the total of ALL sizes - i.e  %DCS = DCSphysAvail / dcSUMphysavail

       

      My expressions are:

      DCSphysAvail   =   Sum({$<warehouse={'DCS'}>}physicalAvailable)

      dcSUMphysavail   =   Aggr(Sum({$<warehouse={'DCS'}>}physicalAvailable),ColourMixI)

      1invexample.png

      As you can see it works ... for ONE ... field only. How do I get dcSUMphysavail to appear in ALL fields for all sizes

       

      I'm looking to obtain output like this (ignore the i.e. part, that is just to show the %DCS calculation values)

       

      itemId  warehouse ColourMix SizeI DCSphysAvail dcSUMphysavail %DCS          

      004827 DCS         GRY0029  2XL  95                                            7.6%  i.e. 95 / 1249

                                                 L     433                  1249                34.7%     433 / 1249

                                                 M    489                                         39.15%   489 / 1249

                                                 S    100                                           8.0%     100 / 1249

                                                 XL   132                                         10.56%    132 / 1249

       

      I'm not fussed if the 1249 value for dcSUMphysavail repeats through the displayed 2XL, L, M, S, XL fields or not. As you can see its not repeating in my case which is why the % DCS calulation isnt working through all the Size fields since I'm using the expression:

       

      %DCS = Sum({$<warehouse={'DCS'}>}physicalAvailable)    /    Aggr(Sum({$<warehouse={'DCS'}>}physicalAvailable),ColourMixI)

       

      Thanks

       

      Cheers