Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a data table I am loading whereby I am first creating a new column which takes the Contract Start Date PLUS the Contract Tenure.
What I wish to do is look at this new date column and create a brand new column which states whether the customer is 'In Contract' or 'Out of Contract'. This would be based on whether the date was before or after the date of the load (I have created a variable vToday which I think I can incorporate.
I cant find anything in community which points me in the right direction.
Any advice/solutions out there?
Thanks
Martin
Message was edited by: Martin Hamilton Added as a sample
Martin,
I noticed in your load script you used AddMonths to add the Contract Tenure to the Contract Start Date. I believe you need to do the same when using the formula from Mindaugas.
So, update the formula to be:
IF(Today() <= AddMonths ([Contract Start Date],[Contract Tenure]) , 'In Contract', 'Out of Contract') as [Is in contract]
From Source
Without the AddMonths, the formula is probably adding the Tenure as days instead of months.
Hope this helps!
- Stan
Martin,
Need a sample to show also not understanding whether load date is column which is already exsting in your data model.
You may create a load date (dashboard refresh date) using below
Date( floor(ReloadTime()),'DD/MM/YYYY') as LoadDate
then create an if statement using the above date to generate in contract column and out of contract column
Siva
Use IF statement in your data model like:
Load
[Contract Start Date]
,[Contract Tenure]
,IF(Today() <= [Contract Start Date] + [Contract Tenure], 'In Contract', 'Out of Contract') as [Is in contract]
From Source;
Hi Mindaugas - I have added the logic you provided into my load script but it doesnt seem to be taking into consideration the actual contract end date but looks at the contact start date. Can you take a look and see where I am going wrong? Because I am creating a new contract end date i was sure whether it was possible to produce a look up as part of the load to generate the new column?
Thanks
Hi Siva - I have uploaded a sample which incorporates the suggested approach from Mindaugas.
Martin,
I noticed in your load script you used AddMonths to add the Contract Tenure to the Contract Start Date. I believe you need to do the same when using the formula from Mindaugas.
So, update the formula to be:
IF(Today() <= AddMonths ([Contract Start Date],[Contract Tenure]) , 'In Contract', 'Out of Contract') as [Is in contract]
From Source
Without the AddMonths, the formula is probably adding the Tenure as days instead of months.
Hope this helps!
- Stan
Thats exactly what it was doing! thanks Stan