topic Re: Aggregate an aggr function in QlikView

Aggregate an aggr function

inescastelhano — Wed, 30 Dec 2015 15:01:05 GMT

Dear all,

I am facing a problem. I have the following table (see below), with Person ID, Event ID, Date, Event Category (A-L), Value per event and:

I want to calculate the value spent per person (since ever) -> that's fine, using an aggr function with person ID (spent = 1 means the person spent some money in that event).

=max(aggr( sum({< [Spent]={1}>}[Value], [Person ID]))

Then I want to calculate the value spent per person per year (having a certain year selected, I want to know how much each person spent for example in 2013). -> I have to aggregate person ID and year.

I'm using this formula:

=max(aggr(max(aggr(

sum({<[Spent]={1} >}[Value])

, [Person ID])), Year))

but it only returns the person who spent most each year, as shown in the picture.

And afterwards I would like to calculate the value spent per person per month (having a certain year and month selected, I want to know how much each person spent for example in January 2015). -> I have to aggregate person ID, year and Month.

Can somebody give me some ideas?

Thanks in advance,

Inês

Re: Aggregate an aggr function

marcus_sommer — Wed, 30 Dec 2015 16:29:21 GMT

Within a table which contained the detail-dimensions you didn't need an aggr-function - they are only useful if you have a consolidated view and wanted to know the aggregation of a calculation on lower dimensions and then you would need sum(aggr(sum for this and not max(aggr(sum.

In your table you need more something like this: sum(TOTAL <Year> Value).

AGGR...

When should the Aggr() function NOT be used?

- Marcus

Re: Aggregate an aggr function

inescastelhano — Wed, 30 Dec 2015 16:45:18 GMT

Hi, thanks for your reply. Unfortunately, what you are saying does not work (because I need set analysis to say spent = 1).

If I take out the aggr function, I get the sum for each row, which means that value per event = value spent per person (since ever). Each row returns a different value.

Re: Aggregate an aggr function

marcus_sommer — Wed, 30 Dec 2015 17:49:32 GMT

I didn't meant to remove the set analysis conditions - this was only for reasons to simplify the logic that you rather need an expression-extension to TOTAL instead to use an aggr-wrapping.

- Marcus