Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Default current fiscal year and month in current selection on active sheet

Hi All,

I am trying to set a default current fiscal year and month in my current selection for sheet.

Any help would be appreciated.

Thanks!!

20 Replies
cspencer3
Creator II
Creator II

Could you use a Document Trigger that does a 'Select In Field' on the action 'OnOpen'  of the document. You could then use a formula to find the current fiscal year (there may be a function for this), and set your select in field value to the calculated fiscal year. This would save you from hard coding the fiscal year into the field. You could do the same for the month.

Not applicable
Author

Yes I did try 'Document trigger'/'OnOpen'/'Select In Feld'/'SearchString = varYear', defining varYear = year(today()) in load script but didn't work.

I ended up using 'Sheet Properties'/'Trigger'/'OnActiveSheet'/'SearchString = Year(Today(1)) it woked well for calendar year , but am looking for fiscal year here.

For list box of fiscal year am using this code in LoadScript

(case when  month(CreatedDt)  < ($(vFirstMonth) then  cast(year(CreatedDt) as varchar(4))

Else cast(year(CreatedDt) +1  as varchar(4)) END) as 'Fiscalyear'

datagrrl
Creator III
Creator III


Have you double checked what your variables are populated as? I will add a text box to print the values until I am sure I have it right.

Sometimes I end up with null values in my variables even though my script is loading fine.

Not applicable
Author

The Variable looks good. I can see current year.

cspencer3
Creator II
Creator II

can you find the current fiscal year by saying something like:

fiscal year = if( month(date) <9, year(date), year(date)+1)

I would use the variable checking method that DataGrrl mentioned as well. Text Boxes are perfect for that!

cspencer3
Creator II
Creator II

Why do you cast the Fiscal Year as a varchar?

For the OnActivateSheet Search String use

Search String = if( month(date) <9, year(date), year(date)+1)

Not applicable
Author

yes varchar.

Didn't work

Not applicable
Author

I also used this variable

Let varCurrentFiscalYear = if(Month(today())< 7, year(today()), Year(today()) + 1)

Not applicable
Author

Of all this only Substring = Year(today(1)) works but which gives calendar year