topic Re: To Display Current Year+2 and Current year -2 in QlikView

To Display Current Year+2 and Current year -2

Tue, 07 Jan 2014 13:33:07 GMT

Hi All,

I have a field called "Production Year"

I need to display " Current year + 2 and Current year -2 "

Eg: Current Year = 2014

I need a list box containing

2012, 2013, 2014(Current year), 2015 and 2016...

Can anyone pls help me with this..

Thanks,

praveen

Re: To Display Current Year+2 and Current year -2

Tue, 07 Jan 2014 13:41:49 GMT

I'd say try creating a master calendar in your load script, in this master calendar use variables for min date which would be your Production Year - 2 and then one for your max date which would be Production Year + 2. This may be a useful post to check out.... Creating A Master Calendar

Re: To Display Current Year+2 and Current year -2

manideep78 — Tue, 07 Jan 2014 14:01:26 GMT

Hi Praveen

Please try the below script:

LET vMin = Year(Today())-2;

LET vMax = Year(Today())+2;

YearTable:

LOAD

$(vMin)+ IterNo()-1 as ProductionYear

AutoGenerate 1 While $(vMin)+ IterNo()-1 <= $(vMax);

Hope this helps

Regards,

Manideep

Re: To Display Current Year+2 and Current year -2

john_duffy — Tue, 07 Jan 2014 14:17:01 GMT

Hi Praveen.

Do you want to show the string 2012,2013, etc. in the list box?

If so, you could create a field called ProdYears_Display:

(ProdYear - 2 ) & chr(44) & (ProdYear - 1) & chr(44) & (ProdYear) & chr(44) & (ProdYear + 1) & chr(44) & (ProdYear + 2) as ProdYears_Display

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 05:38:37 GMT

Hi John Duffy,

Do you want me to write it in the load Script?

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 06:01:29 GMT

Hi Manideep,

Thanks. I got it correct with your script.

I am a newbie. Can you please explain this script to me.?

Thanks,

Praveen

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 06:54:09 GMT

Hi Manideep,

This is actually creating a temporary table. On selecting any date, it is not getting affected to the data. (If I select 2014 from it, it is not showing related data)

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 07:07:12 GMT

Hi Praveen,

Add Date format function here , otherwise display the number,

YearTable:

LOAD
Date($(vMin)+

IterNo()-1,'YYYY')

CurrentYear

AutoGenerate 1

While

$(vMin) +

IterNo()+1 <=

$(vMax);

Re: To Display Current Year+2 and Current year -2

manideep78 — Wed, 08 Jan 2014 08:09:11 GMT

Hi Praveen

Yes, It will create a table with only one field called "ProductionYear". If there is already a field with the same name in any other tables, it will automatically link so that the data may get affected.

Let me know if your issue was resolved other wise send me the Sample app with some sample data so that we can see how we can generate the ProductionYear in the existing data model.

Regards

Manideep

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 08:48:09 GMT

LOAD SHOW_ID AS PROJECT_ID,
SHOW_NAME,
REGION
LOCN
LIC_NBR,
TV_LEVEL_F,
TV_SEASON_F,
TV_SEASON_DESCR_F,
TV_SERIES_F,
TV_SERIES_DESCR_F,
WPR_GENRE_F,

PRDCTN_YR_F,-------->This is the column which has Production Year Data.

PROD_TYPE_F

FROM
[\\ffeuscnm\Fins\VenSpre\SHOW.QVD]
(qvd);

The above is the QVD which I have loaded. Now in editors MAIN Page, I have included the script which u have given me.

LET vMin = Year(Today())-2;
LET vMax = Year(Today())+2;
YearTable:
LOAD
$(vMin)+ IterNo()-1 as ProductionYear
AutoGenerate 1 While $(vMin)+ IterNo()-1 <= $(vMax);

I am getting the Table as how I need. but its not affecting the data. Can u help me with this ?

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 10:03:56 GMT

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 10:04:56 GMT

I am getting the Table as how I need. but its not affecting the data. Can u help me with this ?

Re: To Display Current Year+2 and Current year -2

manideep78 — Wed, 08 Jan 2014 10:08:27 GMT

Praveen,

Change the name of "ProductionYear" as "PRDCTN_YR_F" in my script

or else

change the name of "PRDCTN_YR_F" as "ProductionYear" in your script.

Motto here is to establish link between your table and temp table

Simple change. Now your data will get affected.

Regards,

Manideep

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 10:29:02 GMT

Hi Manideep,

I did those changes already.

I changed the names as "PRDCTN_YR_F" in both.

Again getting an error.

Since the Schema name is attached. I am getting as Yeartable.PRDCTN_YR_F in temp table and QVS_SHOW.PRDCTN_YR_F as names

So I couldnt join.

I tried renaming it, then I faced an issue as the year filter was not working.

i.e. After joining, it is bringing all data from QVS_SHOW.PRDCTN_YR_F. whereas I need only 2012,2013, 2014,2015 and 2016

Re: To Display Current Year+2 and Current year -2

manideep78 — Wed, 08 Jan 2014 10:33:35 GMT

please send me the sample app along with sample data

Re: To Display Current Year+2 and Current year -2

Wed, 08 Jan 2014 11:14:41 GMT

Thanks Manideep.