topic Re: MULTIPLE YEARS AGGREGATION in QlikView

MULTIPLE YEARS AGGREGATION

Thu, 02 Jul 2015 14:45:40 GMT

In the above Pivot table I am trying to Aggregate 2006 thru 2012. So The Out put should look like below, Means I wann show Report Year (Max, Max-1 ,Max-2 and Max-3 shall show me the sum of All Previous Years. My Current Expression is :

Count({<CLAIM_TYPE_CODE_COMPARE_YTD_FLAG={'1'},Year={$(=Max(Year)),$(=Max(Year)-1),$(=Max(Year)-2)},
Report_Year={"<=$(=max(Year)-3)>=$(=min(Year)-10)",$(=Max(Year)),$(=Max(Year)-1),$(=Max(Year)-2)}>} distinct [Claim No]) .

Pls Help

Required OUTPUT

Re: MULTIPLE YEARS AGGREGATION

MK_QSL — Thu, 02 Jul 2015 14:51:16 GMT

Provide 10 line sample data !

Re: MULTIPLE YEARS AGGREGATION

Thu, 02 Jul 2015 14:53:38 GMT

Ok give me a sec pls

Re: MULTIPLE YEARS AGGREGATION

Thu, 02 Jul 2015 15:06:06 GMT

In the Below Sample Data I wanna see sum(Suit) for Report Year 2015,2014,2013

and Report Year (2012,2011,2010,2009,2008) summed together to show for Report Year 2012. So my output will be

Report Year Suit

2015 45

2014 77

2013 50

2012 279

(Sum of Report Years 2012,2011,2010,2009,2008)

Year	Report Year	Suit	NOI
2015	2015	25	30
2015	2015	20	25
2015	2014	30	29
2015	2014	25	35
2015	2014	22	35
2015	2013	10	15
2015	2013	15	39
2015	2013	25	69
2015	2012	26	65
2015	2012	26	37
2015	2012	27	36
2015	2011	22	33
2015	2011	11	32
2015	2010	63	17
2015	2009	32	38
2015	2008	36	38
2015	2008	36	35

Re: MULTIPLE YEARS AGGREGATION

MK_QSL — Thu, 02 Jul 2015 15:16:17 GMT

Create a Pivot Table

Dimension

1) Year

2) Calculated Dimension

=IF([Report Year] <= '2012','2012',[Report Year])

Expression

SUM(Suit)

Re: MULTIPLE YEARS AGGREGATION

Thu, 02 Jul 2015 15:30:29 GMT

Thanx that worked partially, now I wanna make it Dynamic, instead of hard coding the Year '2012'

Like when I select Year 2015, I wanna see

2015

2014

2013

2012 ( Sum of Prev Yrs)

When I select Year 2014, I wanna see

2014

2013

2012

2011 (Sum of Prev Years)

Re: MULTIPLE YEARS AGGREGATION

MK_QSL — Thu, 02 Jul 2015 15:43:46 GMT

Everything need to use as I suggested except calculated Dimension.

Change calculated dimension as below

=Aggr(IF([Report Year] > Max(TOTAL [Report Year])-3, [Report Year],Max(TOTAL [Report Year])-3),[Report Year])