3 Replies Latest reply: Jul 22, 2011 10:06 AM by Stefan Wühl RSS

    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

        • Creating a Fiscal Year Group
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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