Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Resolving syntax in script for '=, <,>,-='

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!

1 Solution

Accepted Solutions
Creator III

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

/////////////////////////////////////////////

[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]

;

[

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

]

;

17 Replies
MVP

Try this

If([Renewal Quarter 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]

Anonymous
Not applicable
Author

Hi Sunny,

It didn't work. Still getting the same result.

Creator III

Hi ,

are these fields [Renewal Quarter 3] and [Renewed On 3] dual or just text?

MVP

thanks

regards

Marco

MVP

Can you test just this to see if it works or not

If([Renewal Quarter 3] = 'Not Renewed', 'IT WORKS', 'IT DOESN'T WORK') as Test

This should at least help us understand if the first part of the if statement is working or not

Anonymous
Not applicable
Author

It didn't work. The expression updated IT DOESN'T WORK in all cells.

Anonymous
Not applicable
Author

Hi Tatsiana,

The data values for both Renewal Quarter 3 & Renewed on 3 is a date transformed into Quarter format.

Ex:

 Renewal Quarter 3 2017-Q2
Anonymous
Not applicable
Author

Hi Marco,

Sample data shown below:

 Renewal Quarter 3 Renewed On 3 Renewal Status 2017-Q2 2016-Q3 Early 2016-Q1 2016-Q3 Late 2016-Q1 2016-Q3 Late 2016-Q1 2016-Q3 Late 2016-Q3 2016-Q3 On Time 2016-Q4 2016-Q3 Early
MVP

Somethins is not right about [Renewal Quarter 3] then... can you check this

If(WildMatch([Renewal Quarter 3], '*Not*Renewed*', 'IT WORKS', 'IT DOESN'T WORK') as Test

Community Browser