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.

ibraheem_qaed
Not applicable

Calculate Future Renewal Date

Dears,

I hope you are well,

I need your help here to convert excel formula to Qlikview logic to calculate the future renewal date as of today.

The excel formula is:

=EDATE(LOOKUP(TODAY(),EDATE(A2,B2*(ROW($A$1:$A$100)-1))),B2)

The result will become in Formula column :

    

StartDate

Contract Period Per Month

EmployeeStatusFormula
2/7/200224JohnRenewed2/7/2018
1/3/201524SergioRenewed1/3/2017
10/5/201024MichaelRenewed10/5/2016
9/9/201424SaraRenewed9/9/2016
1/9/199024JolyRenewed1/9/2018
4/17/201424BoadiceaRenewed4/17/2018
5/3/201424BoadiceaRenewed5/3/2018
6/3/201424BoadiceaRenewed6/3/2016
6/3/201412BoadiceaRenewed6/3/2016

or if there is any other expressions in Qlikview can do that, it would be great.


can any one help me out here ?

Thanks,

Ibrahim

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Calculate Future Renewal Date

Maybe like

RENEW:

LOAD *, AddMonths(StartDate, MonthDiff) as NextRenewalDate;

LOAD *,

Ceil(

  (year(Today())*12)+month(Today())-((year(StartDate)*12)+month(StartDate))-(Day(StartDate) > Day(Today()) )

  ,[Contract Period Per Month]) as MonthDiff

INLINE [

StartDate, Contract Period Per Month,Employee, Status, Formula

2/7/2002, 24, John, Renewed, 2/7/2018

1/3/2015, 24, Sergio, Renewed, 1/3/2017

10/5/2010, 24, Michael, Renewed, 10/5/2016

9/9/2014, 24, Sara, Renewed, 9/9/2016

1/9/1990, 24, Joly, Renewed, 1/9/2018

4/17/2014, 24, Boadicea, Renewed, 4/17/2018

5/3/2014, 24, Boadicea, Renewed, 5/3/2018

6/3/2014, 24, Boadicea, Renewed, 6/3/2016

6/3/2014, 12, Boadicea, Renewed, 6/3/2016

];

6 Replies
marcus_sommer
Not applicable

Re: Calculate Future Renewal Date

I'm not sure if I understand your question right but I think you could replace EDATE() with addmonths() in combination with a makedate() function, maybe something like this:

addmonths(makedate(year(today(), month(StartDate), day(StartDate)), [Contract Period Per Month])

- Marcus

ibraheem_qaed
Not applicable

Re: Calculate Future Renewal Date

Hi Marcus,

I know this but I need to get the future renewal date for each employee. let me explain this, if I was hired in 10-Nov-2010 and my contract period is two years(24 months) and my contract is renewed automatically each two years so my future renewal date or the end of contract date will be on 10-Nov-2016 as per today.

I already got this expression from here but still it shows me that my contract will be ended on 10-Nov-2018

Date(MakeDate(2017 + (-1*Even(Year(StartDate))), Month(StartDate), Day(StartDate))) as [End Of Contract]

the below excel formula is giving me the right information but I am still trying to convert that to QLikview logic to add it in my report :

=EDATE(LOOKUP(TODAY(),EDATE(A2,B2*(ROW($A$1:$A$100)-1))),B2)

However, if there is any expression on QLikview can do that, it will be great.


Thanks,

Ibrahim

swuehl
Not applicable

Re: Calculate Future Renewal Date

Maybe like

RENEW:

LOAD *, AddMonths(StartDate, MonthDiff) as NextRenewalDate;

LOAD *,

Ceil(

  (year(Today())*12)+month(Today())-((year(StartDate)*12)+month(StartDate))-(Day(StartDate) > Day(Today()) )

  ,[Contract Period Per Month]) as MonthDiff

INLINE [

StartDate, Contract Period Per Month,Employee, Status, Formula

2/7/2002, 24, John, Renewed, 2/7/2018

1/3/2015, 24, Sergio, Renewed, 1/3/2017

10/5/2010, 24, Michael, Renewed, 10/5/2016

9/9/2014, 24, Sara, Renewed, 9/9/2016

1/9/1990, 24, Joly, Renewed, 1/9/2018

4/17/2014, 24, Boadicea, Renewed, 4/17/2018

5/3/2014, 24, Boadicea, Renewed, 5/3/2018

6/3/2014, 24, Boadicea, Renewed, 6/3/2016

6/3/2014, 12, Boadicea, Renewed, 6/3/2016

];

marcus_sommer
Not applicable

Re: Calculate Future Renewal Date

Have a closer look on the suggestion from swuehl which returned exact your results in the column "Formula" - you might need to adjust the StartDate within the expression to date#(StartDate, 'MM/DD/YYYY') to make sure it will be treated as a numeric value.

- Marcus

ajsjoshua
Not applicable

Re: Calculate Future Renewal Date

ibraheem_qaed
Not applicable

Re: Calculate Future Renewal Date

Hi Swuehl,

it is working like a charm, thank you so much.

Thanks,

Ibrahim