Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello QV team,
Still a beginner and loving the learning process!
Your help would be awesome with this minor issue:
In the script, I am attempting to determine output in a new field by stating the following:
If Date 1 = Date 2; 'On Time'
If Date 1 > Date 2; 'Early'
If Date 1 < Date 2; 'Late'
If Date 1 <> Date 2; ' ' (keep blank).
Formula shown below:
IF([Renewal Quarter 3]=[Renewed On 3],'On Time',IF([Renewal Quarter 3]<[Renewed On 3],'Late',IF([Renewal Quarter 3]>[Renewed On 3],'Early',IF([Renewal Quarter 3]<>[Renewed On 3],'','')))) as [Renewal Status]
For the fourth condition, I've also attempted the following:
If Date 1 ='Not Renewed', ' ' (keep blank).
Formula shown below:
IF([Renewal Quarter 3]=[Renewed On 3],'On Time',IF([Renewal Quarter 3]<[Renewed On 3],'Late',IF([Renewal Quarter 3]>[Renewed On 3],'Early',IF([Renewal Quarter 3]='Not Renewed','','')))) as [Renewal Status]
The result for the last condition is shown below and is incorrect.
Any help would be appreciated!
Thanks!
You didn't add a case where you would get ' '... it might help to add that case also
what about the 'Not Renewed' and blank values?
The data below shows Renewal Status incorrectly labeled as Late. The goal is to reflect any row that has not been renewed to stay blank or marked as Not Renewed as well.
Renewal Quarter 3 | Renewed On 3 | Renewal Status |
2014-Q3 | Not Renewed | Late |
2014-Q3 | Not Renewed | Late |
2015-Q1 | Not Renewed | Late |
2015-Q1 | Not Renewed | Late |
2015-Q1 | Not Renewed | Late |
2014-Q3 | Not Renewed | Late |
Renewal quarter 3 is derived from another date in the same row.
Formula below:
If(len([End Date])>0, Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3),'') as [Renewal Quarter 3]
Renewed On 3 is derived from a mapping function:
Formula below:
if(len(ApplyMap('Key_Table',[EK1],''))>0,Year(ApplyMap('Key_Table',[EK1],''))&'-'&'Q'& Ceil(Month(ApplyMap('Key_Table',[EK1],''))/3),'Not Renewed') as [Renewed On 3],
Hope this helps.
Hi Mona,
Quick question for you)
can 'Not Renewed' value be only in Renewed On 3 or in both fields?
as I understand from your formulas for creating this fields this value can be only in Renewed On 3.
if yes, than in your for formula you just need to change the name
If([Renewed On 3] = 'Not Renewed', '',
If([Renewal Quarter 3] = [Renewed On 3], 'On Time',
If([Renewal Quarter 3] < [Renewed On 3], 'Late',
If([Renewal Quarter 3] > [Renewed On 3], 'Early')))) as [Renewal Status]
an example
/////////////////////////////////////////////
load
[Renewal Quarter 3]
, [Renewed On 3]
, If([Renewed On 3] = 'Not Renewed', ' ',
If([Renewal Quarter 3] = [Renewed On 3], 'On Time',
If([Renewal Quarter 3] < [Renewed On 3], 'Late',
If([Renewal Quarter 3] > [Renewed On 3], 'Early')))) as [Renewal Status]
;
load * Inline
[
Renewal Quarter 3, Renewed On 3
2014-Q3, Not Renewed
2015-Q1, Not Renewed
2015-Q2, Not Renewed
2014-Q1, Not Renewed
2017-Q2, 2016-Q3
2016-Q1, 2016-Q3
2016-Q1, 2016-Q3
2016-Q1, 2016-Q3
2016-Q3, 2016-Q3
2016-Q4, 2016-Q3
]
;
Make sure all the values in your Date fields are really, really date values and not strings that merely look like dates.
Hi Gysbert,
Not sure whether the formula below is turning the value into a string:
If(len([End Date])>0, Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3),'') as [Renewal Quarter 3],
Tatsiana!!! You are awesome!!
Thank you it worked!!!
Sharing a virtual cup of coffee with you at the moment!
Cheers and have an awesome Wednesday!
Yes, that expression returns strings.