topic Advanced aggregation in QlikView

Advanced aggregation

Tue, 24 Nov 2009 01:26:14 GMT

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!

Advanced aggregation

Anonymous — Tue, 24 Nov 2009 10:46:11 GMT

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.

Advanced aggregation

Wed, 25 Nov 2009 00:55:39 GMT

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!

Advanced aggregation

Wed, 25 Nov 2009 02:45:12 GMT

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

Tom

Advanced aggregation

Wed, 25 Nov 2009 17:41:56 GMT

Thanks, works perfectly.