topic Aggregated total per period in QlikView

Aggregated total per period

nsm1234567 — Mon, 08 Jun 2015 10:57:15 GMT

Hi There,

I'm having an issue aggregating revenue per consultant. I've attached a model to illustrate the issue. The output I'm looking for is the below, where each fin period holds the total for each consultant, but can't seem to get it right. Can anyone let me know where I'm going wrong? Any help would be greatly appreciated.

Consultant	HEADER	Acc Desc	Fin Period	1	2
Jim Johnson	Revenue Proportion			1748345	2239373
John Smith	Revenue Proportion			1748345	2239373

Message was edited by: Nathan S Added attachment

Re: Aggregated total per period

qlikmsg4u — Mon, 08 Jun 2015 10:58:53 GMT

Hi Nathan,

Please attach the sample app

Re: Aggregated total per period

nsm1234567 — Mon, 08 Jun 2015 11:06:55 GMT

Apologies, I attached it now.

Re: Aggregated total per period

rubenmarin — Mon, 08 Jun 2015 11:19:56 GMT

Hi Nathan,

In your 2nd table you can add Fin Period and Consultant as dimensions for the TOTAL:

If(HEADER = 'Revenue Proportion',

sum(TOTAL <[Fin Period], Segment3Desc> {$<AEFLAG={1}, Segment3Desc=, REPORT = {"IncomeStatement"},[Fin Year] = {$(vMaxYear)}, DESCRIPTION = {'Revenue'}>} Movement))

In your 3rd table you can add Consultant to the Aggr():

Aggr(sum({$<AEFLAG={1}, Segment3Desc=, REPORT = {"IncomeStatement"},

[Fin Year] = {$(vMaxYear)}, DESCRIPTION = {'Revenue'}>} Movement),[Fin Period], Segment3Desc)