# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## 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!

17 Replies
Highlighted
MVP

You didn't add a case where you would get ' '... it might help to add that case also

Highlighted
MVP

what about the 'Not Renewed' and blank values?

Highlighted
Contributor III

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.

Highlighted
Specialist

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

]

;

Highlighted
MVP & Luminary

Make sure all the values in your Date fields are really, really date values and not strings that merely look like dates.

talk is cheap, supply exceeds demand
Highlighted
Contributor III

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

Highlighted
Contributor III

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!

Highlighted
MVP & Luminary

Yes, that expression returns strings.

talk is cheap, supply exceeds demand