Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Creating a Fiscal Year Group

Hi,

I have an application that contains a calendar table which identifies an ISO date (week one is January) and a fiscal date (week one is from April 1)

We also have a fiscal system date (Week 1 is from 26/03/2011)

What I need to be able to do, is to identify which Year these dates relate to. The problem I have is trying to create a year which identifies that 26/03/2011 is in Year 2012

25/03/2011 should then be the last day of the previous year (2011)

Hopefully someone can help

Thanks in advance!

Ben

3 Replies
MVP
MVP

Creating a Fiscal Year Group

Hi Benjy_25,

hi Benji, I think you need to determine the rule / algorithm that defines the year start to e.g. 26.3.2011.

Given that, it should be easy to derive the FY year (or is it years (fiscal system date and fiscal date differ?)?. I have not understood that, honestly).

Could you post the script expressions that you used to set your different dates / weeks?

Regards,

Stefan

Not applicable

Re: Creating a Fiscal Year Group

Hi Stefan,

Thanks for the reply

The example would be...I have these dates, but I need them to sit within different financial years and weeks e.g.

    

DATE          ISO Week       Fin Week      Sys Fin Week    ISO Year      Fin Year      sys fin year

25/03/2011      12                     52                   52                2011             2011             2011

26/03/2011      13                     52                    1                 2011             2011             2012

27/03/2011      13                     52                    1                 2011             2011             2012

01/04/2011      13                     1                      1                 2011             2012             2012

The expressions i have are:

week(weekstart(ACCOUNT_DATE,1,-2)) = ISO week

week(weekstart(ACCOUNT_DATE,-11,-2)) = Finance week

date(yearstart(ACCOUNT_DATE,1,4)) = Finance Year

Thanks

Ben

MVP
MVP

Re: Creating a Fiscal Year Group

Hi,

where have you got the expressions from?

I would have assumed that ISO week is just week(DATE).

(I think and believe QlikView uses ISO 8601 for week number calculation).

But your definition let week start on Saturday, right? But then it's not ISO anymore

Ok, your FInance Year starts 01/04. (thats means 31/03/2011 is FY2011, 01/04/2011 is FY2012.)

But I am not sure that it's a good idea to hardcode Finance week by using

week(weekstart(ACCOUNT_DATE,-11,-2)) = Finance week

i.e let it start on Saturday (ok, whatever) but use ISO week number from 11 weeks earlier. Hm, don't think that this will be correct for every week in the future.

But of course I don't know your requirements, so I take that and think about getting the years.

Leaving sys financial week / year, where I am missing an expression like for the others.

Maybe you could clarify that for me,

Stefan

Community Browser