Advanced aggregation

[#1]

Hi,

We have a bunch of daily profit and loss numbers with each days data being tagged with 4 product levels, e.g.

Date = 1st Jan 2009

L0 = A

L1 = B

L2 = C

L3 = D

P&L = 100

We want to be able to generate a table which, for each product shows how many days had a negative P&L. This is fine when we hardcode the product level, however we want to be able to drill up/down through the product levels (L0->L3) and aggregate the number of days with a negative P&L. This is I suppose the equivalent of doing an expression like:

count(distinct if(sum(P&L)<0, Date))

Clearly though this won't work as you can't nest aggregation functions in this way.

Is there another way of doing this?

Thanks!

[Anonymous]

Hi,

You should be able to use Aggr() between the aggregation functions to aggregate the first sum over the dimension value.

count(distinct aggr(if(sum(P&L)<0, Date), Dimension))

If your dimension is a group consisting of dimensions L0-L3 you should be able to use getcurrentfield( myGroup ) to return the currently active dimension to the expression like:

count(distinct aggr(if(sum(P&L)<0, Date), getcurrentfield(myGroup)))

This as always depends on what your actual document looks like.

[#3]

Johannes, thanks for the quick reply.

I could get it to work though as it wouldn't let me use the group dimension in the expression. Have mocked up a quick example of what I am trying to do and attached it. All assistance greatly appreciated!

[#4 · Accepted solution]

Take a look at this one, only slight correction from Johannes suggestion.

Tom

[#5]

Thanks, works perfectly.