Need the fiscal year as a variable

[QlikSensor]

Hi,

I need the fiscal year as a variable during load. The data load is exectured on a daily basis via a daily trigger. 

I need this to setup an if statement in different diagrams.

Thanks for supporting.

[JordyWegman]

Hi,

Because fiscal years are company dependent, you will need to have this information in your calendar.

If you have this calendar you can extract this:

Calendar:
Load
  [Fiscal year]
From [YourSource] (qvd)
Where %Date = floor(today());  // Assumption is that you have a floored date

Let vCurrentFiscalYear = Peek('Fiscal year',0,'Calendar')

Now you have your variable vCurrentFiscalYear.

Jordy

Climber

[QlikSensor]

Hm,

I haven't this calendar. Our fy starts on 1.10.

I would need for today 2019 and on 1.10.2019 2020. Can't this be done more easily during load?

[JordyWegman]

Yes this is possible of course, use this formula:

if(date(Date,'MMDD')&'' >= 1001 & '' and date(Date,'MMDD')&'' <= 1231 & '' ,Year(Date) + 1,Year(Date))	as [Fiscal year],

Jordy

Climber 

[jonathandienst]

Or simpler:

Year(AddMonths(Date, 3)) as [Fiscal Year],