topic Re: Aggr within Expression with Set Analysis in QlikView

Aggr within Expression with Set Analysis

danielle_v — Tue, 02 Sep 2014 15:33:23 GMT

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!

Re: Aggr within Expression with Set Analysis

MK_QSL — Tue, 02 Sep 2014 15:35:21 GMT

Try

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

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

Simply

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

Re: Aggr within Expression with Set Analysis

jagan — Tue, 02 Sep 2014 15:45:19 GMT

Hi,

Try this expression

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

Regards,

Jagan.

Re: Aggr within Expression with Set Analysis

evan_kurowski — Tue, 02 Sep 2014 16:24:21 GMT

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.

Re: Aggr within Expression with Set Analysis

danielle_v — Mon, 22 Sep 2014 11:02:03 GMT

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

Re: Aggr within Expression with Set Analysis

PrashantSangle — Mon, 22 Sep 2014 11:08:31 GMT

Hi,

Try this,

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

Regards