topic Creating a Fiscal Year Group in QlikView

Creating a Fiscal Year Group

Fri, 22 Jul 2011 10:25:39 GMT

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

Creating a Fiscal Year Group

swuehl — Fri, 22 Jul 2011 10:52:21 GMT

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

Re: Creating a Fiscal Year Group

Fri, 22 Jul 2011 13:27:42 GMT

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

Re: Creating a Fiscal Year Group

swuehl — Fri, 22 Jul 2011 14:06:18 GMT

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