Aggr within Expression with Set Analysis

[danielle_v]

Hi All,

I am trying to put together an expression which includes aggr and set analysis, and I have come unstuck.

The starting point for my expression is the following, which (I think) is giving me the UnitPrice multiplied by the ShipmentQuantity at ShipmentOrder level, and then aggregating this up so that I don't need to use ShipmentOrder as a dimension in my chart

sum ( aggr ( ( UnitPrice * sum ( ShipmentQuantity ) ) , ShipmentOrder ) )

I now need to add some set analysis into this expression, to include only ShipmentOrders where the Department is 'Clothing'.

Among other things I have tried the following, but nothing seems to be giving me the output I would expect;

sum ( ( { $ < Department = { "Clothing" } > } aggr ( ( UnitPrice * sum ( ShipmentQuantity ) ), ShipmentOrder) )

Any pointers on this would be greatly appreciated, as I'm not sure what to try next!

Thanks in advance!

[MK_QSL]

Try

sum ( aggr ( ( UnitPrice * sum ({< Department = { "Clothing" }>} ShipmentQuantity ) ) , ShipmentOrder ) )

or

=SUM(Aggr(SUM({<Department = {'Clothing'}>}(UnitPrice * ShipmentQuantity)),ShipmentOrder))

or

Simply

=SUM({<Department = {'Clothing'}>}(UnitPrice * ShipmentQuantity))

[jagan]

Hi,

Try this expression

Sum ( {< Department = { "Clothing" }>} aggr ( ( sum ( {< Department = { 'Clothing'}>} UnitPrice * ShipmentQuantity ) ) , ShipmentOrder ) )

Regards,

Jagan.

[evan_kurowski]

Hello Danielle,

Part of what may be crucial in this expression is the placement of UnitPrice within your expression.  If you have multiple UnitPrice values per ShipmentOrder then your syntax needs to account for that, and very subtle differences can change your results:

There are many possible syntaxes that will produce your calculation, so maybe what also would help is understanding why the initial calculation was not producing the result you expected.

If you intend to not use ShipmentOrder as a dimension in your chart, perhaps you could show some specific example of what dimensions you expect to use, because otherwise as has been pointed out you may not even need the Aggr() expression at all.

As Manish pointed out, a filtered sum of "clothing" could be:
Sum({<Department={'Clothing'}>} UnitPrice * Quantity)

I could see involving AGGR() in cases where you might be trying to calculate things like Average $ Per Order or Average Quantity per Order in charts where the ShipmentOrder was not a dimension.

[danielle_v]

Hi Evan,

Apologies for the delay on this, and thank you very much for your help.

My charts will be looking at calculations such as sum of UnitPrice by department, or country, or overall for the current (selected) vs. previous (set by a variable) month.

The reason I believe I need to add aggr into my expression (and I may well be wrong, I'm a total beginner when it comes to these kinds of expressions) is that different ShipmentOrders will have different UnitPrices. My understanding is that just multiplying UnitPrice by Quantity would be fine if I was to do a chart where the dimension is ShipmentOrder, but it will fall down as soon as I evaluate by any other dimension?

I hope the below example will help make the issue a little clearer;

ShipmentOrder          UnitPrice          Quantity          UnitPrice * Quantity

1                                   3.0                    100                    300

2                                   5.0                     200                   1000

3                                   2.0                     50                     100                                      

               Total UnitPrice * Quantity = 1400

Thanks again for your help,

Danielle

[PrashantSangle]

Hi,

Try this,

Sum(if(Department='Clothing',aggr (UnitPrice * sum(ShipmentQuantity), ShipmentOrder)))

Regards