Discussion Board for collaboration on QlikView Scripting.
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!
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?
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
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
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,