Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

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

in the attachment the qvw I used for testing

10 Replies
MVP
MVP

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

Your fiscal year 2012/13 starts from which month?

Not applicable

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

It would start on 01/08 every year

MVP
MVP

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

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
Contributor II

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

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

MVP
MVP

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

what is the logic of 2012 and 2013 in anniversary ?

Not applicable

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

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?

MVP
MVP

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

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

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

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

MVP
MVP

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

in the attachment the qvw I used for testing

Community Browser