topic Fiscal Year On Set Analysis Filtering in QlikView

Fiscal Year On Set Analysis Filtering

idodel — Sat, 16 Nov 2024 19:17:50 GMT

Hello Qliks,

I saw some threads about something like my issue but did not manage to find exact same.

I need to both filter on set analysis the current Financial Year (FY20) and in another field the previous one (FY19)

I do have fields that contain years (YYYY), FY (FY15,FY16,FY17...),MonthYear(201901,201902....)

FY is from June to May.

I need it to be dynamic so when the next FY arrives, it will happen automatically.

current filter on set analysis : FYear={'FY20'}

Can I split the 'FY20' into dynamic variable?

Thanks,

Ido

gavinlaird — Wed, 15 Jan 2020 23:45:29 GMT

You will want to define the current FY in the load script:

MaxFY: LOAD Max(Right(FYear,2)) as CurrentFY Resident [your data]; Let vCurrentFY = 'FY'&Peek('CurrentFY'); Drop Table MaxFY;

Then, you can use the vCurrentFY variable in your set analysis, instead of using the hard value, "FY20".

{<FYear={$(vCurrentFY)}>}

idodel — Thu, 16 Jan 2020 11:29:29 GMT

thank you for your comment!

finally i managed to solve it by creating 2 variables holding current and previous FY 🙂