topic Calendars in QlikView

Calendars

Anonymous — Fri, 24 Oct 2014 08:25:14 GMT

Within a calendar, can you limit the years to be displayed in your application. Lets say you have data covering the following years:

2009

2010

2011

2012

2013

2014

Can i restrict my calendar to say the last three years or something like that? Thank you in advance.

Re: Calendars

jonathandienst — Fri, 24 Oct 2014 08:32:46 GMT

You might get better help if you were more specific. Do you want to load only the last three years? Or display the last three years in a chart or table? Do you want a calculated dimension, or a set expression?

For example, you could use this in a set expressiom:

{<Year = {'>=$(=Max(Year) - 3)'}>}

HTH

Jonathan

Re: Calendars

Anonymous — Sat, 25 Oct 2014 10:49:19 GMT

Re: Re: Calendars

Anonymous — Sat, 25 Oct 2014 10:50:37 GMT

Thank you. I want my calendar to load the last three years. How do i accomplish this?

Re: Re: Calendars

its_anandrjs — Sat, 25 Oct 2014 12:29:12 GMT

Hi,

In the load script you can achieve this by

Data:

LOAD * Inline

[

Year

2009

2010

2011

2012

2013

2014

]

Where Year >= Year( Today() ) - 2 AND Year <= Year( Today() );

Regards,

Anand

Re: Re: Calendars

its_anandrjs — Sat, 25 Oct 2014 12:40:38 GMT

Hi,

For better explain i load another table with only last latest years

Data:

LOAD * Inline

[

Year

2009

2010

2011

2012

2013

2014 ];

Filtered:

LOAD

Year as Last3Year

Resident Data Where Year >= Year(Today())-2 and Year <= Year(Today());

Regards,

Anand

Re: Calendars

markodonovan — Sat, 25 Oct 2014 12:52:18 GMT

Hi Anand,

Excellent example.

I guess you could also keep the Where clause even simpler if you didn't have any years in the future such as 2015.

Filtered:

LOAD

Year as Last3Year

Resident Data

Where Year >= Year(Today())-2;

Thanks

Mark

www.techstuffy.com

Re: Calendars

its_anandrjs — Sat, 25 Oct 2014 13:45:54 GMT

Thanks Mark, Yes it is right way also.

Regards

Anand

Re: Calendars

SunilChauhan — Sat, 25 Oct 2014 14:55:38 GMT

below condition is worth to write

AND Year <= Year( Today()

only

Where Year >= Year( Today() ) - 2 condition will do the trick

see example below

Load Year

if( Year >= Year( Today() ) - 2,Year ) as Last3years;

LOAD * Inline

[

Year

2009

2010

2011

2012

2013

2014

]

;

Re: Calendars

its_anandrjs — Sat, 25 Oct 2014 15:15:44 GMT

There is another approach for the same as Sunil suggest by using Flag field if you want to keep both the fields and also do not need to create another table.

Ex:-

Load

Year,

if( Year >= Year(Today())-2, 1, 0) as Last3YrFlag;

LOAD * Inline

[ Year

2009

2010

2011

2012

2013

2014 ];

But in the expression you have to write SET expression

Sum( {<Last3YrFlag ={'1'} >} Metrics )

Regards

Anand

Re: Calendars

SunilChauhan — Sat, 25 Oct 2014 15:21:35 GMT

thanks anand .

we could also use conditions without if as they are consuming memory

Load

where Year >= Year( Today() ) - 2;

LOAD * Inline

[

Year

2009

2010

2011

2012

2013

2014

]

;

Re: Re: Calendars

Anonymous — Sun, 26 Oct 2014 13:16:03 GMT

Thank you all for your valuable inputs, most appreciated.

Re: Calendars

Sun, 26 Oct 2014 13:19:25 GMT

Hi,

In the load script you can achieve this by

Data:

LOAD * Inline

[

Year

2009

2010

2011

2012

2013

2014

]

Where Year >= Year( Today() ) - 2 AND Year <= Year( Today() );

Re: Calendars

Anonymous — Mon, 27 Oct 2014 06:48:25 GMT

Hi Many thanks