topic Re: How to find the anniversary of a start date in a particular year? in QlikView

How to find the anniversary of a start date in a particular year?

Tue, 29 Jul 2014 08:53:24 GMT

START_DATE	Anniversary of start date in 2012/3
15/09/2008	15/09/2012
26/01/2010	26/01/2013
13/09/2010	13/09/2012
31/03/2011	31/03/2013
17/09/2012	17/09/2012
24/01/2013	24/01/2013

Hi all,

I have figures for the start date of employees (column 1 above) and need something in the script to work out the anniversary of their start date in the current year. I have used 2012/3 as an example. I am looking to use the date in the first column to find the date in the second column which I do not have.

Any help would be much appreciated as I have been going round in circles on this one

Many thanks

Re: How to find the anniversary of a start date in a particular year?

MK_QSL — Tue, 29 Jul 2014 09:01:40 GMT

Your fiscal year 2012/13 starts from which month?

Re: How to find the anniversary of a start date in a particular year?

Tue, 29 Jul 2014 09:04:02 GMT

It would start on 01/08 every year

Re: How to find the anniversary of a start date in a particular year?

maxgro — Tue, 29 Jul 2014 09:05:39 GMT

result and expression

makedate(

if(MakeDate(2000, month(START_DATE), Day(START_DATE)) <MakeDate(2000, month(today()), Day(today())),

Year(Today())+1,Year(Today()) )

,month(START_DATE)

,Day(START_DATE)

)

script

load * inline [

START_DATE

15/09/2008

26/01/2010

13/09/2010

31/03/2011

17/09/2012

24/01/2013

];

Re: How to find the anniversary of a start date in a particular year?

tobias_klett — Tue, 29 Jul 2014 09:06:43 GMT

Hi Lucy,
I'm assuming your 2012/3 is from July2012 till June2013. Therefore I would use.
=makedate(day(START_DATE),num(month(START_DATE)),if(num(month(START_DATE))>6,2012,2013)

Hope this helps
Tobias

Re: How to find the anniversary of a start date in a particular year?

MK_QSL — Tue, 29 Jul 2014 09:16:41 GMT

what is the logic of 2012 and 2013 in anniversary ?

Re: How to find the anniversary of a start date in a particular year?

Tue, 29 Jul 2014 09:25:27 GMT

This appears to be working but how would I get the dates to fall in 2012/3 rather than 2014/5? Would I let Year(Today())-1?

Re: How to find the anniversary of a start date in a particular year?

maxgro — Tue, 29 Jul 2014 09:29:33 GMT

if you want 2 years before current year

makedate(

if(MakeDate(2000, month(START_DATE), Day(START_DATE)) <MakeDate(2000, month(today()), Day(today())),

Year(Today())-1,Year(Today())-2 )

,month(START_DATE)

,Day(START_DATE)

)

Re: How to find the anniversary of a start date in a particular year?

Tue, 29 Jul 2014 09:30:04 GMT

For some reason this was only bringing up the year and not the rest of the date but will play around and see if I can make it work

Many thanks

Re: Re: How to find the anniversary of a start date in a particular year?

maxgro — Tue, 29 Jul 2014 09:33:01 GMT

in the attachment the qvw I used for testing

Re: Re: How to find the anniversary of a start date in a particular year?

Tue, 29 Jul 2014 09:38:05 GMT

Thank-you so much for your help, works a treat