Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitians
Creator III
Creator III

InYearToDate Nested Function

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

3 Replies
tresesco
MVP
MVP

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


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

rohitians
Creator III
Creator III
Author

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
MVP
MVP

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