How do I label data based on whether a datefield occurs before or after today's date?

[#1]

Hello, I wonder if you can help?

I have a data set where the end date of a contract is recorded as a date for those whose contract has ended, and as a blank field (but it seems to be in date format) for those whose contract hasn't ended. I want to load this into my app as 'Contract Ended' or 'Current Contract'

I tried the following expression:

If(Index("contract_end_Date",(<=07/12/2015)), "Contract Ended", 'Current Contract'), 

But when the script loads is says : ')' expected.

I am basing this on a piece of scripting that has worked in the same app which was as follows:

If(Index(e_mail, '@'), 'Has email', 'No email'),

Can anyone help?

Thank you!

Loren Dean-Austin

[#2]

Hi,

You might try something like this in a preceding load:

load

contract_end_date,

    contractID,

if(date#(contract_end_date)<=today(), 'contract ended', 'contract current') as contractStatus;

data:

load * inline [

contract_end_date, contractID

'12/03/2015', 1

'12/21/2016', 2

];

This example gives the following result

[reddy-s]

Hi Loren

Index() is not the right function, i would suggest you to go with the "if" condition instead.

[#4]

Hi Sangram,

Thank you for this. I have tried to use the below, with no luck (it doesn't recognise anything as a 'contract ended', and thinks that everything in the app is 'contract current')

if(date#(contract_end_dt)<=today(), 'contract ended', 'contract current') as TenancyStatus,

Would this have been the way you would have written the formula using the "if" condition?

Thanks

Loren

[reddy-s]

Hi Loren,

You have not mentioned the format of the date in the date# function.

try this: if(date#(contract_end_dt,'MM/DD/YYYY')<=today(), 'contract ended', 'contract current') as TenancyStatus,

just replace the 'MM/DD/YYYY' with the format of your date string and try please

[#6]

Hi Sangram

I have tried this, unfortunately it still hasn’t worked. It still recognises every customer as ‘contract current’, even though there are at least half that have a null value in the contract_end_dt field.

Is it because they are null values that this isn’t working?

Thank you

Loren

Loren Dean-Austin

Customer Insight Analyst

Business Excellence

ForViva

52 Regent Street

Eccles

M30 0BP

T: 0161 605 7921

E: loren.dean-austin@forviva.co.uk<mailto:loren.dean-austin@forviva.co.uk>

W: www.forviva.co.uk<http://www.forviva.co.uk>