topic Re: How to determine End_date from Start_date in QlikView

How to determine End_date from Start_date

Thu, 05 Nov 2015 10:57:44 GMT

Hi Guys,

Please can any one help me to determine the end date of a contract from the start date? I only have the contract period in numbers such as a two year contract beginning 1/1/2015 has a contract period 24.See example below. So Please how can I determine the end date with thesame format as the start date in a Table in Qlikview based on the Contract_Period@@?

ID Start_date End_date Contract_period E.g End_date

1 1/1/2015 ? 24 12/31/2016

2 2/10/2015 ? 24

3 5/15/2015 ? 24

4 6/10/2015 ? 24

5 7/20/2015 ? 24

Note: Date Format is "MM/DD/YYYY"

Re: How to determine End_date from Start_date

Clever_Anjos — Thu, 05 Nov 2015 12:00:27 GMT

Maybe

Date(AddMonths(Start_date,Contract_Period),'MM/DD/YYYY')

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 12:23:55 GMT

Hi Clever, I added the statement to the Calendar script but it did not work.

Re: How to determine End_date from Start_date

Clever_Anjos — Thu, 05 Nov 2015 12:43:17 GMT

Would you mind sharing your data or sample of your app?

Re: How to determine End_date from Start_date

Anonymous — Thu, 05 Nov 2015 12:58:36 GMT

Try this one:

date(AddMonths(date(StartDate,'DD/MM/YYYY'),ContractPeriod),'DD/MM/YYYY') as EndDate

Hope this will help!!

Re: How to determine End_date from Start_date

marcus_malinow — Thu, 05 Nov 2015 13:03:47 GMT

how about

Date(AddMonths(date(StartDate, 'MM/DD/YYYY'), ContractPeriod) - 1, 'MM/DD/YYYY')

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 13:07:59 GMT

Bu the problem is the Startdate and Contract Period reside in two different table. So using the Resident function it give me an error saying the contract period cannot be found.

Re: How to determine End_date from Start_date

Anonymous — Thu, 05 Nov 2015 13:10:00 GMT

Don't do the above in the calendar script, but in the actual table with the data.

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 13:13:59 GMT

So Kim Should I add as an expression?

Re: How to determine End_date from Start_date

Anonymous — Thu, 05 Nov 2015 13:16:32 GMT

Try at script level

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 13:32:06 GMT

Hi Clever,

I wanna thank you for your help. I tried it as a calculated dimension and it worked.

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 13:41:19 GMT

Thanks Kim I did in the table and it worked.

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 13:51:15 GMT

Hi Clever Please another question,

Now how can I label this column as End_date in the Table?

Re: How to determine End_date from Start_date

Thu, 05 Nov 2015 13:52:27 GMT

Hi Kim,

Any idea how I can give the column a name. Since I added it as i calculated dimension i can figure out how to assign a label to it

Re: How to determine End_date from Start_date

Anonymous — Thu, 05 Nov 2015 21:57:47 GMT

Date(AddMonths(Start_date,Contract_Period),'MM/DD/YYYY') AS YourDesiredFieldName

Re: How to determine End_date from Start_date

Anonymous — Thu, 05 Nov 2015 22:02:18 GMT

Oh, sorry - I didn't see that you added as calculated dimension. You can only give it a label in each object. But really, do it in the script. Since this is a static value per row of data, it is just waste performing this calculation on each user interaction (even though cache will help some).