topic Re: Aggregation Sum and Running Total by YEAR in QlikView

Aggregation Sum and Running Total by YEAR

Wed, 21 May 2014 17:13:52 GMT

Hello everyone;

I have a pivot table in which I have YEAR and CA( Sales) . At the beginning, I want to display CA for each Year , But in the End of my pivot table I want to display the sum of CA of the last 3 years like the table bellow.

YEAR	CA	YEAR	CA	CA 2
2004	30 808 147	2004	30 808 147	30 808 147
2005	48 861 126	2005	48 861 126	48 861 126
2006	81 700 925	2006	81 700 925	81 700 925
2007	65 124 463	2007	65 124 463	65 124 463
2008	107 623 593	2008	107 623 593	107 623 593
2009	99 820 060	2009	99 820 060	72 113 460
2010	89 784 698	2010	89 784 698	99 820 060
2011	137 087 828	2011	137 087 828	342 468 400
2012	133 267 111	2012	133 267 111
2013	72 113 460	2013	72 113 460
Total	866 191 411	TOTAL	866 191 411	866 191 411

Is It possible to display That table in Qlikview ??

Re: Aggregation Sum and Running Total by YEAR

whiteline — Wed, 21 May 2014 19:15:23 GMT

Hi.

You can use calculated dimension to join the years like this:

=aggr(if(Year<2011, Year, '2011-2013'), Year)

Re: Aggregation Sum and Running Total by YEAR

its_anandrjs — Wed, 21 May 2014 20:27:18 GMT

Write an expression like the attached sample script

Dimension:- YEAR

Expr1:- Sum(CA)

Expr2:- if(YEAR = 2011, sum(TOTAL {<YEAR ={2011,2012,2013}>} CA),sum({<YEAR ={2004,2005,2006,2007,2008,2009,2010}>} CA))

Then You get out put :-

Re: Aggregation Sum and Running Total by YEAR

Thu, 22 May 2014 08:55:32 GMT

Thank you Anand Chouhan for your replay, It works

But how about merge and center for that 3 years i the table ?? Is it possible. ??

Re: Aggregation Sum and Running Total by YEAR

vipin_mishra479 — Thu, 22 May 2014 09:34:36 GMT

Write an expression like the attached sample script

Dimension:- YEAR

Expr1:- Sum(CA)

Expr2:- if(YEAR = 2011, sum(TOTAL {<YEAR ={2011,2012,2013}>} CA),if(YEAR < 2011,sum(CA), ' ' )

Regards,

vipin

Re: Aggregation Sum and Running Total by YEAR

its_anandrjs — Thu, 22 May 2014 20:35:33 GMT

Thanks, I believe that is not possible i let you know if that should also worked and provide details to you.

Regards