Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

START_DATEAnniversary of start date in 2012/3
15/09/200815/09/2012
26/01/201026/01/2013
13/09/201013/09/2012
31/03/201131/03/2013
17/09/201217/09/2012
24/01/201324/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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

in the attachment the qvw I used for testing

View solution in original post

10 Replies
MK_QSL
MVP
MVP

Your fiscal year 2012/13 starts from which month?

Not applicable
Author

It would start on 01/08 every year

maxgro
MVP
MVP

result and expression

1.png

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

t:

load * inline [

START_DATE

15/09/2008

26/01/2010

13/09/2010

31/03/2011

17/09/2012

24/01/2013

];1.png1.png

tobias_klett
Partner - Creator II
Partner - Creator II

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

MK_QSL
MVP
MVP

what is the logic of 2012 and 2013 in anniversary ?

Not applicable
Author

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?

maxgro
MVP
MVP

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)

  )

Not applicable
Author

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

maxgro
MVP
MVP

in the attachment the qvw I used for testing