topic Re: year from Date full Format in QlikView

year from Date full Format

krishna20 — Thu, 13 Nov 2014 12:56:25 GMT

Hi Friends,

How can we achieve only current ,previous ,before previous year from full pledged format of date field.

E.g;

Date_FullFormat

12/11/2014

14/12/2013

12/10/2012

14/11/2010

14/06/2004

15/08/2000

I need to show only current ,previous ,before previous year like 2014,2013,2012.

I need to write it in list box expression.Please anyone help me out.

Regards

Krishna

Re: year from Date full Format

Thu, 13 Nov 2014 13:03:11 GMT

try like this

max(year(date_fullformat) as year,

max(year(date_fullformat)-1 as yyear .........

Re: year from Date full Format

anbu1984 — Thu, 13 Nov 2014 13:12:27 GMT

Check this

Re: year from Date full Format

krishna20 — Thu, 13 Nov 2014 13:38:50 GMT

Hi Anbu,

Thank you for your reply,its working purely.But, there is a performance issue with this expression.its taking time to filter the years when we selected the values in the list box.what will be the issue,can u explain it clearly?

Thanks & Regards

Krishna

Re: year from Date full Format

Thu, 13 Nov 2014 13:48:50 GMT

Hi,

Year(today()) as current_year

year(today())-1 as Prev_year,

year(today())-2 as second_prev_year

Re: year from Date full Format

its_anandrjs — Thu, 13 Nov 2014 13:55:51 GMT

Hi,

Yes there is performance issue when we use some calculated expression in the list box it calculate and create the list but when large data set is there and many calculation is available in the list box. Better if you can create one field in the script and then use it for the previous year and PPYear. Or you can try this in the front end also with expression i believe it works well also.

Write this in the list box if you have year field in the script

=if(Year <= Year(Today()) and Year >= Year(Today())-2,Year)

Or in the load script

Years:

LOAD Date(Date#(Date_FullFormat,'DD/MM/YYYY'),'DD/MM/YYYY') AS Date_FullFormat,

Year(Date(Date#(Date_FullFormat,'DD/MM/YYYY'),'DD/MM/YYYY')) as Year;

LOAD * Inline

[

Date_FullFormat

12/11/2014

14/12/2013

12/10/2012

14/11/2010

14/06/2004

15/08/2000

];

Left Join

LOAD

Year,

if(Year <= Year(Today()) and Year >= Year(Today())-2,1,0) as PreviousYear

Resident Years;

Regards

Anand

Re: year from Date full Format

anbu1984 — Thu, 13 Nov 2014 14:03:15 GMT

As Anand said, performance issue is due to calculation done at UI.

You can create Year field in script and use that in List box.

Load *,If(Match(Year(Today())- Year(Dt),1,2,0),Year(Dt)) As Yr;

Load Date#(Dt,'DD/MM/YYYY') As Dt Inline [

12/11/2014

14/12/2013

12/10/2012

14/11/2010

14/06/2004

15/08/2000 ];