topic How to Build date from year in QlikView

How to Build date from year

Thu, 10 Sep 2009 17:32:11 GMT

Hi,

I have Calender with Year. I would Like define a Variable, wenn i selected to Year in Calender, the result from 2 Variables are as date:

1) first day of Year

2)Last Year of Year f

or Example :

i select the Year 2009

VarMin = 01.01.2009

Var Max = 31.12.2009

Thanks

How to Build date from year

Thu, 10 Sep 2009 18:30:51 GMT

I have tried a number of options but the only one I could get to work was the following.

Create two variables:

vMinDate ='01/01/'&min(Year)

vMaxDate ='31/12/'&min(Year)

These variables can then be used in expressions, for example if 2009 is selected vMinDate = 01/01/2009, you can add 30 days to this and the expression =date(vMinDate+30) returns 31/01/2009 etc

I am sure there is a smarter way but this does work.

When I load my tables I create a separate Date table using the script below. Every possible date between Jan 2006 and now is generated. I link this table to for example the orders table using invoice_date.

Then if I were to apply your formula I could simply type =min(invoice_date)

Date:

load

today()-recno()+1 as Date

autogenerate(today()-'2005-12-31');

DateParts:

load

Date as invoice_date,

Date as JJ_Date,

Date as Date,

Year(Date) as Year,

yearname ( Date, 0, 4 ) as FinancialYear,

date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,

date(week(Date),'WW-YYYY') as Date_WeekYear,

Month(Date) as FiscalRollMth,

Month(Date) &'-'&right(Year(Date),2) as RollShortMth,

Month(Date) as Month,

Day(Date) as Day,

Week(Date) as Week,

'Q' & Ceil(Month(Date)/3) as Quarter,

Weekday(Date) as WeekDay

resident Date;

drop table Date;

How to Build date from year

Thu, 10 Sep 2009 18:33:26 GMT

Hi Shideh

I'd suggest using MakeDate( Year, 1, 1 ) and MakeDate( Year, 12, 31 ).

You can write a macro that is triggered by any selection in the Year field. Then set a variable to the value of only( Year ) and use MakeDate() like above.

regards,

Hellmar

How to Build date from year

prieper — Thu, 10 Sep 2009 22:35:00 GMT

Hi
be cautious with MakeDate(Year, 12, 31), if dealing with timestamps, as each timestamp having a time attached, will be bigger than this date. Would recommend to use the EndYear-function.

Peter

How to Build date from year

Thu, 10 Sep 2009 22:45:53 GMT

Use the instructions

Star = YearStar(Today()); = '01-01-2009'

End = YearEnd(Today()); = '31-12-2009'

Good by

How to Build date from year

Mon, 14 Sep 2009 13:39:33 GMT

Hi,

Thanks. how can i use the EndYear for variable (Year). the Year can between 2005 and 2009.

AW:Re: How to Build date from year

rbecher — Mon, 14 Sep 2009 19:35:14 GMT

If it's a field then like this:

YearStart(getfieldselections(Year,'; ',1))

if it's a variable:

YearStart(Year)

- Ralf

AW:Re: How to Build date from year

Mon, 14 Sep 2009 19:39:37 GMT

Hi Ralf,

Thank you very much.

i am happy with this.