laguna92651

Combining date field and text on report

Discussion created by laguna92651 on Jul 8, 2018
Latest reply on Jul 9, 2018 by laguna92651

I have a sub summary report that is being sorted by date periods. These are the labels that I currently have on the break headers.

  Jul 7, 2018 To Aug 7, 2016

  Jul 8, 2018 To Aug 31, 2018

  Aug 31, 2018 To Sep 30, 2018

  Oct 31, 2018 To Jun 7, 2020

 

These are the labels I would like to have.

  Past Due

  Jul 8, 2018 To Aug 31, 2018

  Aug 31, 2018 To Sep 30, 2018

  Oct 31, 2018 To Future

 

This is the calculation for the To Label field (this is a date field), the From Label and the Bracket Sort field are the similar in form. My question is, as an example, how can I replace the x-700, Jun7, 2020, date on line 17 with the text "Future".

--- To Let( [ x = Get ( CurrentDate ) ;        d = Date_Initial ;      level1 =    T14_PREFERENCES::g_funnel_mo_1 ;      level2 = T14_PREFERENCES::g_funnel_mo_2 ;      level3 = T14_PREFERENCES::g_funnel_mo_3      ];      Case( d > Date ( level3 + Month(x) +1 ; 0 ; Year(x) ) ; x + 700 ; d > Date ( level2 + Month(x) +1 ; 0 ; Year(x) ) and d = Date ( level3 +  Month(x) +1 ; 0 ; Year(x) ) ; Date ( level3 +  Month(x) +1 ; 0 ; Year(x) ) ; d > Date ( level1 + Month(x) +1 ; 0 ; Year(x) ) and d = Date ( level2 +  Month(x) +1 ; 0 ; Year(x) ) ; Date ( level2 +  Month(x) +1 ; 0 ; Year(x) ) ; d  = x and d  =   Date ( level1 + Month(x) +1 ; 0 ; Year(x) ) ; Date ( level1 + Month(x) +1 ; 0 ; Year(x) ) ; x -700 ) )

 

--- To
Let( [ x = Get ( CurrentDate ) ;
       d = Date_Initial ;
     level1 =    T14_PREFERENCES::g_funnel_mo_1 ;
     level2 = T14_PREFERENCES::g_funnel_mo_2 ;
     level3 = T14_PREFERENCES::g_funnel_mo_3
     ]; 
   
Case(

 

d > Date ( level3 + Month(x) +1 ; 0 ; Year(x) ) ; x + 700 ;
d > Date ( level2 + Month(x) +1 ; 0 ; Year(x) ) and d = Date ( level3 +  Month(x) +1 ; 0 ; Year(x) ) ; Date ( level3 +  Month(x) +1 ; 0 ; Year(x) ) ;
d > Date ( level1 + Month(x) +1 ; 0 ; Year(x) ) and d = Date ( level2 +  Month(x) +1 ; 0 ; Year(x) ) ; Date ( level2 +  Month(x) +1 ; 0 ; Year(x) ) ;
d  = x and d  =   Date ( level1 + Month(x) +1 ; 0 ; Year(x) ) ; Date ( level1 + Month(x) +1 ; 0 ; Year(x) ) ;
x -700 )
)

Outcomes