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

[#1]

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)

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

[#2 · Accepted solution]

I figured it out 🙂

using NODISTINCT:

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

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

I get this now which is what I wanted:

[johnw]

Perhaps one of these:

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

sum({<warehouse={'DCS'}>} total <ColourMixI> physicalAvailable)

Edit:  Heh.  I was too slow.

[#4]

Precisely! Thanks for replying, I must have posted my solution a second ahead of yours. Clearly QV'ing with a head cold is slow going for my brain today and inspiration only starts after I give up and post on the community.

Thanks for giving an example using TOTAL function - that is a very useful alternative approach for me to memorise.

Now I have TWO ways of doing this 🙂