InYearToDate Nested Function

[rohitians]

Hi Friends,

I  want to create a flag for calender and fiscal year Fiscal year starts from 01-11-2017 to 31-10-2018

I tried InYearToDateFunction  it works best for current year as follows,

fabs(InYearToDate(Floor(TempDate), today(), 0, 11)) AS FinYr20182Date, 

  fabs(InYearToDate(Floor(TempDate), today(), 0, 1)) AS CalYr20182Date,

but what about previous years I am not able to write the code please help,

For work around I am hard coding as follows,

Can this be dyanamic??

Please Help.

    if(Floor(TempDate)>='2019-11-01' and TempDate<='2020-10-31' ,1,0) as FinYr2020Date,

  if(Floor(TempDate)>='2020-01-01' and TempDate<='2020-12-31' ,1,0) as CalYr2020Date,

    if(Floor(TempDate)>='2018-11-01' and TempDate<='2019-10-31' ,1,0) as FinYr2019Date,

  if(Floor(TempDate)>='2019-01-01' and TempDate<='2019-12-31' ,1,0) as CalYr2019Date,

  if(Floor(TempDate)>='2017-11-01' and TempDate<='2018-10-31' ,1,0) as FinYr2018Date1,

  if(Floor(TempDate)>='2018-01-01' and TempDate<='2018-12-31' ,1,0) as CalYr2018Date2,

   if(Floor(TempDate)>='2016-11-01' and TempDate<='2017-10-31' ,1,0) as FinYr2017Date,

  if(Floor(TempDate)>='2017-01-01' and TempDate<='2017-12-31' ,1,0) as CalYr2017Date,

   if(Floor(TempDate)>='2015-11-01' and TempDate<='2016-10-31' ,1,0) as FinYr2016Date,

  if(Floor(TempDate)>='2016-01-01' and TempDate<='2016-12-31' ,1,0) as CalYr2016Date,

   if(Floor(TempDate)>='2014-11-01' and TempDate<='2015-10-31' ,1,0) as FinYr2015Date,

  if(Floor(TempDate)>='2015-01-01' and TempDate<='2015-12-31' ,1,0) as CalYr2015Date,

   if(Floor(TempDate)>='2013-11-01' and TempDate<='2014-10-31' ,1,0) as FinYr2014Date,

  if(Floor(TempDate)>='2014-01-01' and TempDate<='2014-12-31' ,1,0) as CalYr2014Date,

  if(Floor(TempDate)>='2012-11-01' and TempDate<='2013-10-31' ,1,0) as FinYr2013Date,

  if(Floor(TempDate)>='2013-01-01' and TempDate<='2013-12-31' ,1,0) as CalYr2013Date,

Thanks,

Rohit Yadav

[tresesco]

You could utilize the third parameter of InYearToDate(), like:

InYearToDate(Floor(TempDate), today(), -1, 11)             // -1 means previous year here

[rohitians]

Hi tresesco‌

But it wont be dynamic I still have to specify Date and base Date for each year,

like this

  fabs(InYearToDate(Floor(TempDate), today(), 0, 11)) AS FinYr20182Date, 

  fabs(InYearToDate(Floor(TempDate), today(), 0, 1)) AS CalYr20182Date,

  fabs(InYearToDate('2016-11-01', '2017-10-31', -1, 11)) AS FinYr20172Date, 

  fabs(InYearToDate('2017-01-01', '2017-12-31', -1, 1)) AS CalYr20172Date,

is that correct.

Thanks,

Rohit Yadav

[tresesco]

Have a look here : Year-over-Year Comparisons   and The As-Of Table