topic Re: Last 3 Years in QlikView

Last 3 Years

Tue, 03 Jun 2014 20:26:50 GMT

I need to show only last 3 years in a listbox from the table below. Can someone tell me if I am writing this function correctly? Thanks so much in advance!!!! See the table below:

= if(fyear=currentyr, if(fyear=currentyr-1, if(fyear=currentyr-2, fyear)))

In Expressions box, this function shows no error but I don't see this working or showing only 2014, 2013, 2013 in the listbox.

	fyear	currentyr
Record 1	2009	2014
Record 2	2009	2014
Record 3	2010	2014
Record 4	2010	2014
Record 5	2011	2014
Record 6	2012	2014
Record 7	2011	2014
Record 8	2012	2014
Record 9	2013	2014
Record 10	2013	2014
Record 11	2014	2014
Record 12	2014	2014
Record 13	2014	2014

Re: Last 3 Years

Anonymous — Tue, 03 Jun 2014 20:33:32 GMT

Try using calculated dimension instead of Record (assuming the filed name is Record):

aggr(if(fyear>=(currentyr-2), Record), Record)

Re: Last 3 Years

Tue, 03 Jun 2014 20:34:08 GMT

See attached, I just used =if(currentyr - fyear <= 3, Record) as the expression for the list box. Hope this helps!

Re: Last 3 Years

Tue, 03 Jun 2014 20:38:43 GMT

This one totally works!!! Thanks!!!

Re: Last 3 Years

Tue, 03 Jun 2014 20:39:42 GMT

I didn't try this one. The previous one worked for me. Thanks guys!!

Re: Last 3 Years

suzel404 — Tue, 03 Jun 2014 20:52:04 GMT

Hi npatnaik,

In straight table : Expression :

Only({<fyear={$(=Max({1}fyear)-2), $(=Max({1}fyear)-1), $(=Max({1}fyear))}>} fyear)

Only({<fyear={">=$(=Max(fyear)-2)<=$(=Max(fyear))"}>} fyear)

Re: Last 3 Years

Fri, 06 Jun 2014 00:59:29 GMT

Michael -- Can you help me solve this problem as well?? Your last answer was bang on!!!

Calculate Contribution % for last 3 Years only