16 Replies Latest reply: Nov 30, 2011 11:54 AM by Tracy Crown RSS

    Fiscal Calendar

    Tracy Crown

      Dear all

       

      I am new to QV, can someone help me why YTD column is not working correctly in Q4-2011 ?.

       

      Thank you very much

      Tracy

        • Re: Fiscal Calendar
          Stefan Wühl

          I believe your problem is that you use something like num(month(Date)) as Month. Thus you create a numerical range 1 to 12 for Month, for all years. If you need to cross calendar year bounderies, e.g. if you need to some fiscal year to date, your set expression Month = {"<=$(=max({<Year={$(=max(Year))}>} Month)) will - in the example given- result only in the set of Month values {1,2,3}, the month {4,5,6,7,8,9,10,11,12} of the previous calendar year are missing.

           

          I suggest that you use a numerical value here for month, which is contiguous across year bounderies, I think you could use monthstart(Date), and if you want to format it like Jan / Feb etc,  maybe

           

          Date(monthstart(Date),'YYYY-MMM') as YearMonth

           

          Please take care that your Date is a recognized date type field.

           

          You could also flag your Dates with an additional Fiscal year maybe using

          Date(yearstart(Date,0,4),'YYYY') as FY

           

          This makes it easier to limit your Dates in a FY then. You could do similar to get fiscal periods (P1 equals April etc.)

           

          If you create more and more fields in your calendar, you probably find it helpful to build a master calendar and link your fact table to that. Search for master calendar, there are some examples here in the forum.

           

          Hope this helps,

          Stefan

           

           

            • Re: Fiscal Calendar
              Tracy Crown

              Hi Stefan

               

              I have tried to change the script according to your suggestion below but it does not work at all, you may amend the script and send back to me.

               

              Date(monthstart(Date),'MMM') as Month

              Date(yearstart(Date,0,4),'YYYY') as FY

               

              Thanks a lot

              Tracy

               

               

               

               

              Date: Sun, 20 Nov 2011 11:54:37 -0500

              From: qliktech@sgaur.hosted.jivesoftware.com

              To: tracycrown@hotmail.com

              Subject: - Re: Fiscal Calendar

               

               

               

               

               

               

               

               

               

               

               

              QlikCommunity

               

               

              Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

               

               

               

              I believe your problem is that you use something like num(month(Date)) as Month. Thus you create a numerical range 1 to 12 for Month, for all years. If you need to cross calendar year bounderies, e.g. if you need to some fiscal year to date, your set expression Month = {"<=$(=max({<Year={$(=max(Year))}>} Month)) will - in the example given- result only in the set of Month values {1,2,3}.

               

              I suggest that you use a numerical value here for month, which is contigouus across year bounderies, I think you could use monthstart(Date), and if you want to format it like Jan / Feb etc,  maybe

               

              Date(monthstart(Date),'MMM') as Month

               

              Please take care that your Date is a recognized date type field.

               

              You could also flag your Dates with an additional Fiscal year maybe using

              Date(yearstart(Date,0,4),'YYYY') as FY

               

              This makes it easier to limit your Dates in a FY then. You could do similar to get fiscal periods (P1 equals April etc.)

               

              If you create more and more fields in your calendar, you probably find it helpful to build a master calendar and link your fact table to that. Search for master calendar, there are some examples here in the forum.

               

              Hope this helps,

              Stefan

               

              Reply to this message by replying to this email -or- go to the message on QlikCommunity

              Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

              © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                • Re: Fiscal Calendar
                  Stefan Wühl

                  Hi Tracy,

                   

                  I've noticed that your source table don't inlcude a full financial year, since the dates are the beginning are from year 2000, then there is a big gap until Jan 2011. I assume that this is by accident and I've modified the table and added some more data, just to demonstrate (It will also work with your original data, but then you can't see any data for the FY months, of course).

                   

                  Please note that I added my suggested two expressions to your table load script, they seem to work just fine. In general, I would suggest to use a master calendar which has an date entry for each day, regardless if any Amount is linked to that date. This makes it possible to select those dates (you will notice that there are some months, e.g. in FY 2011, that if you you select on, will not show a correct FYtD value, this is because here we have missing values in the date, resp YearMonth fields. A master calendar would fix that. There are some examples available here in the forum, just search for master calendar).

                   

                  I then replaced the year list box in the UI with FY and used this as expression in your table:

                   

                  =num(sum({$<FY = {$(=max(FY))}, Year=,Quarter=, YearMonth = {"<=$(=max(YearMonth))"},Month= >} Amount), '$#,##0;($#,##0)')

                   

                  So, I set FY to the max possible FY value, and limit YearMonth to YearMonth smaller or equal the max possible value. All other date fields the user may select on need to be cleared.

                   

                  Hope this helps,

                  Stefan

                    • Re: Fiscal Calendar
                      Tracy Crown

                      Dear Stefan

                       

                      Thank you very much, YTD is working now.

                       

                      I don't understand the meaning of the following script, please advise:

                       

                      1. sum({$<FY = {$(=max(FY))},

                      2. Year=,

                      3. Quarter=,

                      4. YearMonth = {"<=$(=max(YearMonth))"},Month= >}

                       

                      In addition, any good suggestion on QTD ?. I had tried YearQuarter but it does not work.

                       

                      Thanks a lot

                      Tracy

                        • Re: Fiscal Calendar
                          Stefan Wühl

                          Tracy,

                           

                          a QTD could look like:

                          =num(sum({$<FY = {$(=max(FY))}, Year=, Quarter=, QNum= {$(=max(QNum))} , YearMonth = {"<=$(=max(YearMonth))"}, Month= >} Amount), '$#,##0;($#,##0)')

                           

                          Here I created a numerical Quarter field, so I can use Max() function on that (well, I could have also used a dual field value or maybe maxstring() function instead).

                           

                           

                          I don't understand the meaning of the following script, please advise:

                           

                          1. sum({$<FY = {$(=max(FY))},

                          2. Year=,

                          3. Quarter=,

                          4. YearMonth = {"<=$(=max(YearMonth))"},Month= >}

                           

                           

                          A part in the set expression like Year= is telling QV to disregard any selection on that year.  FY= {$(=max(FY))} is explicitely setting the max possible value for our financial year FY to this field (so only one value is selected and used for calculation).

                          YearMonth = {"<=$(=max(YearMonth))"} is using a selection for YearMonth that includes any possible value lower or equal the current maximum possible value, so if you select e.g. 2011-10, any YearMonth from the beginning up to 2011-10 is being used, as far as not excluded by another selection, e.g. in field FY.

                           

                          If you are not familiar with point in time calculations using set analysis, maybe you want to have a look into

                          http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

                           

                          which is probably explaining things better than I do.

                           

                          Regards,

                          Stefan

                            • Re: Fiscal Calendar
                              Tracy Crown

                              Dear Stefan

                               

                              You are good, your recommendation on QTD is correct.

                               

                              I have just tested that MTD, YTD and QTD will not work after creating a user selection called "Day (15, 28, 29, 30,31), please advise what should be changed.

                               

                              Thank you very much

                              Tracy

                               

                               

                               

                               

                              Date: Thu, 24 Nov 2011 12:28:42 -0500

                              From: qliktech@sgaur.hosted.jivesoftware.com

                              To: tracycrown@hotmail.com

                              Subject: - Re: Fiscal Calendar

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                              QlikCommunity

                               

                               

                              Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

                               

                               

                               

                              Tracy,

                               

                              a QTD could look like:

                              =num(sum({$}

                               

                               

                               

                              A part in the set expression like Year= is telling QV to disregard any selection on that year.  FY= {$(=max(FY))} is explicitely setting the max possible value for our financial year FY to this field (so only one value is selected and used for calculation).

                              YearMonth = {"<=$(=max(YearMonth))"} is using a selection for YearMonth that includes any possible value lower or equal the current maximum possible value, so if you select e.g. 2011-10, any YearMonth from the beginning up to 2011-10 is being used, as far as not excluded by another selection, e.g. in field FY.

                               

                              If you are not familiar with point in time calculations using set analysis, maybe you want to have a look into

                              http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

                               

                              which is probably explaining things better than I do.

                               

                              Regards,

                              Stefan

                               

                              Reply to this message by replying to this email -or- go to the message on QlikCommunity

                              Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

                              © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                                • Fiscal Calendar
                                  Stefan Wühl

                                  Tracy,

                                   

                                  what do you mean with user selection called Day (15,28,29,30,31) ? Have you created an additional field using Day(Date) as Day or something like this?

                                    • Re: Fiscal Calendar
                                      Tracy Crown

                                      Dear Stefan

                                       

                                      Yes, I had created a Day field using day(Date) and all (MTD, QTD & YTD) fail to work properly, kindly advise what should be changed ?.

                                       

                                      Thank you

                                      Tracy

                                       

                                       

                                       

                                       

                                      Date: Sat, 26 Nov 2011 11:17:03 -0500

                                      From: qliktech@sgaur.hosted.jivesoftware.com

                                      To: tracycrown@hotmail.com

                                      Subject: - Re: Fiscal Calendar

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                      QlikCommunity

                                       

                                       

                                      Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

                                       

                                       

                                       

                                      Tracy,

                                       

                                      what do you mean with user selection called Day (15,28,29,30,31) ? Have you created an additional field using Day(Date) as Day or something like this?

                                       

                                      Reply to this message by replying to this email -or- go to the message on QlikCommunity

                                      Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

                                      © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                                        • Re: Fiscal Calendar
                                          Stefan Wühl

                                          Tracy,

                                           

                                          I added a small master calendar and changed the set expressions to be date limited instead of month limited. Please check the attached file. It is important to clear all date fields the user can select on that may interfere with our fields we use to limit the date range.

                                           

                                          Regards,

                                          Stefan

                                            • Re: Fiscal Calendar
                                              Tracy Crown

                                              Dear Stefan

                                               

                                              Well done, I am so happy to see that everything is working fine.

                                               

                                              Kindly help to explain the following as I am new to Qlikview :

                                               

                                              1. dual(date(Date,'MMM'),pick(month(Date),10,11,12,1,2,3,4,5,6,7,8,9)) as Month;

                                               

                                                  What is the purpose of dual(date) and why need to pick  ?

                                               

                                              2. Date(makedate(2010,01,01)+recno()-1,'DD/MM/YYYY') as Date

                                                  What is recno()-1 for ?

                                               

                                              3. AutoGenerate 2*365+91;

                                                  Why 91 ?

                                               

                                              Thank you very much

                                              Tracy

                                               

                                               

                                               

                                               

                                              Date: Sat, 26 Nov 2011 20:31:17 -0500

                                              From: qliktech@sgaur.hosted.jivesoftware.com

                                              To: tracycrown@hotmail.com

                                              Subject: - Re: Fiscal Calendar

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                               

                                              QlikCommunity

                                               

                                               

                                              Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

                                               

                                               

                                               

                                              Tracy,

                                               

                                              I added a small master calendar and changed the set expressions to be date limited instead of month limited. Please check the attached file. It is important to clear all date fields the user can select that may interfere with our fields we use to limit the date range.

                                               

                                              Regards,

                                              Stefan

                                               

                                              Reply to this message by replying to this email -or- go to the message on QlikCommunity

                                              Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

                                              © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                                                • Re: Fiscal Calendar
                                                  Stefan Wühl

                                                  1. dual(date(Date,'MMM'),pick(month(Date),10,11,12,1,2,3,4,5,6,7,8,9)) as Month;

                                                   

                                                      What is the purpose of dual(date) and why need to pick  ?

                                                   

                                                  2. Date(makedate(2010,01,01)+recno()-1,'DD/MM/YYYY') as Date

                                                      What is recno()-1 for ?

                                                   

                                                  3. AutoGenerate 2*365+91;

                                                      Why 91 ?

                                                   

                                                   

                                                  1.

                                                  dual( s , x )

                                                  Forced association of an arbitrary string representation s with a given number representation x.

                                                   

                                                  Here we are forcing an association between e.g. month name Jan and 10, Feb and 11 etc. month() of a date in january date will return 1, but here I use pick to get the corresponding lookup value for our fiscal period (=10).

                                                  Maybe this all is a little confusing, I introduced this to get a list box sort order of your month values in fiscal period order, please check the list box in the UI. You will notice that the month list starts with Apr.

                                                   

                                                  2.

                                                  recno() returns an integer for the number of the currently read row of the input table. The first record is number 1. Since I want my calendar to start with January 1st,2010, I need to subtract 1.

                                                   

                                                  3.

                                                  Ok, that must be confusing. 2*365+91 will just create an abitray number of days for your calendar (2 years and 3 months in my example, 3 months to finish the fiscal year 2011).

                                                   

                                                   

                                                  Hope that clarified everything, have fun

                                                  Stefan

                                                    • Re: Fiscal Calendar
                                                      Tracy Crown

                                                      Dear Stefan

                                                       

                                                      Appreciate all your kind effort and professional advice.

                                                       

                                                      I know I have a lot to learn, will need more help from you next time.

                                                       

                                                      Thank you very much

                                                      Tracy

                                                       

                                                       

                                                       

                                                       

                                                      Date: Mon, 28 Nov 2011 12:23:52 -0500

                                                      From: qliktech@sgaur.hosted.jivesoftware.com

                                                      To: tracycrown@hotmail.com

                                                      Subject: - Re: Fiscal Calendar

                                                       

                                                       

                                                       

                                                       

                                                       

                                                       

                                                       

                                                       

                                                       

                                                       

                                                       

                                                      QlikCommunity

                                                       

                                                       

                                                      Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

                                                       

                                                       

                                                       

                                                       

                                                      1. dual(date(Date,'MMM'),pick(month(Date),10,11,12,1,2,3,4,5,6,7,8,9)) as Month;

                                                       

                                                          What is the purpose of dual(date) and why need to pick  ?

                                                       

                                                      2. Date(makedate(2010,01,01)+recno()-1,'DD/MM/YYYY') as Date

                                                          What is recno()-1 for ?

                                                       

                                                      3. AutoGenerate 2*365+91;

                                                          Why 91 ?

                                                       

                                                       

                                                      1.

                                                      dual( s , x )

                                                      Forced association of an arbitrary string representation s with a given number representation x.

                                                       

                                                      Here we are forcing an association between e.g. month name Jan and 10, Feb and 11 etc. month() of a date in january date will return 1, but here I use pick to get the corresponding lookup value for our fiscal period (=10).

                                                      Maybe this all is a little confusing, I introduced this to get a list box sort order of your month values in fiscal period order, please check the list box in the UI. You will notice that the month list starts with Apr.

                                                       

                                                      2.

                                                      recno() returns an integer for the number of the currently read row of the input table. The first record is number 1. Since I want my calendar to start with January 1st,2010, I need to subtract 1.

                                                       

                                                      3.

                                                      Ok, that must be confusing. 2*365+91 will just create an abitray number of days for your calendar (2 years and 3 months in my example, 3 months to finish the fiscal year 2011).

                                                       

                                                       

                                                      Hope that clarified everything, have fun

                                                      Stefan

                                                       

                                                      Reply to this message by replying to this email -or- go to the message on QlikCommunity

                                                      Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

                                                      © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                                      • Re: Fiscal Calendar
                                        Tracy Crown

                                        Dear Stefan

                                         

                                        Thank you very much, YTD is working now.

                                         

                                        I don't understand the meaning of the following script, please advise:

                                         

                                        1. sum({$} 

                                         

                                         

                                        In addition, any good suggestion on QTD ?. I had tried YearQuarter but it does not work.

                                         

                                        Thanks a lot

                                        Tracy

                                         

                                         

                                        Date: Wed, 23 Nov 2011 07:03:14 -0500

                                        From: qliktech@sgaur.hosted.jivesoftware.com

                                        To: tracycrown@hotmail.com

                                        Subject: - Re: Fiscal Calendar

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                        QlikCommunity

                                         

                                         

                                        Re: Fiscal Calendar created by swuehl in Development (QlikView Desktop) - View the full discussion

                                         

                                         

                                        Hi Tracy,

                                         

                                        I've noticed that your source table don't inlcude a full financial year, since the dates are the beginning are from year 2000, then there is a big gap until Jan 2011. I assume that this is by accident and I've modified the table and added some more data, just to demonstrate (It will also work with your original data, but then you can't see any data for the FY months, of course).

                                         

                                        Please note that I added my suggested two expressions to your table load script, they seem to work just fine. In general, I would suggest to use a master calendar which has an date entry for each day, regardless if any Amount is linked to that date. This makes it possible to select those dates (you will notice that there are some months, e.g. in FY 2011, that if you you select on, will not show a correct FYtD value, this is because here we have missing values in the date, resp YearMonth fields. A master calendar would fix that. There are some examples available here in the forum, just search for master calendar).

                                         

                                        I then replaced the year list box in the UI with FY and used this as expression in your table:

                                         

                                        =num(sum({$} Amount), '$#,##0;($#,##0)')

                                         

                                        So, I set FY to the max possible FY value, and limit YearMonth to YearMonth smaller or equal the max possible value. All other date fields the user may select on need to be cleared.

                                         

                                        Hope this helps,

                                        Stefan

                                         

                                        Reply to this message by replying to this email -or- go to the message on QlikCommunity

                                        Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

                                         

                                        © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                                  • Re: Fiscal Calendar
                                    rohit gupta

                                    hi

                                    please find the attachment

                                    but i  am not able to understand what is your fiscal year

                                    thanks

                                    rohit

                                      • Re: Fiscal Calendar
                                        Tracy Crown

                                        Hi Rohit

                                         

                                        The fiscal calender should start from Apr 2010 (mistake in worsheet as 2000) to Mar 2011.

                                         

                                        // Quarter, Month, MonthName

                                        // Q1, 4, Apr

                                        // Q1, 5, May

                                        // Q1, 6, Jun

                                        // Q2, 7, Jul

                                        // Q2, 8, Aug

                                        // Q2, 9, Sep

                                        // Q3, 10, Oct

                                        // Q3, 11, Nov

                                        // Q3, 12, Dec

                                        // Q4, 1, Jan

                                        // Q4, 2, Feb

                                        // Q4, 3, Mar

                                         

                                        Thanks a lot

                                        Tracy

                                         

                                         

                                         

                                        Date: Mon, 21 Nov 2011 00:53:41 -0500

                                        From: qliktech@sgaur.hosted.jivesoftware.com

                                        To: tracycrown@hotmail.com

                                        Subject: - Re: Fiscal Calendar

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                         

                                        QlikCommunity

                                         

                                         

                                        Re: Fiscal Calendar created by rohit214 in Development (QlikView Desktop) - View the full discussion

                                         

                                         

                                         

                                        hi

                                        please find the attachment

                                        but i  am not able to understand what is your fiscal year

                                        thanks

                                        rohit

                                         

                                        Reply to this message by replying to this email -or- go to the message on QlikCommunity

                                        Start a new discussion in Development (QlikView Desktop) by email or at QlikCommunity

                                        © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                                      • Re: Fiscal Calendar
                                        rohit gupta

                                        hi trcy

                                        it won't show because fiscal year starts from April to march

                                        but in your database date lies in Q3 .if you want to see data in Q4 then data should be

                                        like 1/1/2012 to 31/3/2012 beacuse it will count in fiscal year of 2011 Q4

                                        may it helps you to understand

                                        what i want to say

                                        thanks &best regard

                                        rohit