# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
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
MVP

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

MVP

what about the 'Not Renewed' and blank values?

Contributor III
Author

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.

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

]

;

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
Contributor III
Author

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

Contributor III
Author

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.

talk is cheap, supply exceeds demand
Community Browser