issue in result

[soniasweety]

Hi all,

@sunny_talwar

i am using below  code . 

If(mid(EndDate,6,2) > 9,Left(EndDate,4)&'Q4',           if(mid(EndDate,6,2)>6 ,Left(EndDate,4)&'Q3',

                       if(mid(EndDate,6,2)>3,Left(EndDate,4)&'Q2',                                    Left(EndDate,4)&'Q1'))) AS  NewQtr,

its working fine. but  in the  result values   i m getting   Q1 also as a value.

like below

2018Q1

2018Q2

2018Q3

2018Q4

Q1

2017Q1....  so on.   why that "Q1"  also coming?      where is the issue?

best

sony

[pradosh_thakur · Accepted solution]

looks like a data  issue to me. May be null are creating the issue

try the below.

If(mid(EndDate,6,2) > 9,Left(EndDate,4)&'Q4',           if(mid(EndDate,6,2)>6 ,Left(EndDate,4)&'Q3',

                       if(mid(EndDate,6,2)>3,Left(EndDate,4)&'Q2',  
 if(mid(EndDate,6,2)>0, Left(EndDate,4)&'Q1'))) )AS  NewQtr

or

if( len(trim(EndDate))>0 ,If(mid(EndDate,6,2) > 9,Left(EndDate,4)&'Q4',           if(mid(EndDate,6,2)>6 ,Left(EndDate,4)&'Q3',

                       if(mid(EndDate,6,2)>3,Left(EndDate,4)&'Q2',                                    Left(EndDate,4)&'Q1')))) AS  NewQtr