Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
rohitians
Contributor 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
MVP
MVP

Re: InYearToDate Nested Function

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


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

rohitians
Contributor III

Re: InYearToDate Nested Function

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

MVP
MVP

Re: InYearToDate Nested Function

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