12 Replies Latest reply: Apr 29, 2015 4:10 AM by Joanna Seldon RSS

    Financial Quarter

    Joanna Seldon

      hiya

       

      I am using the following in my load script using Date_Received... in order to get my month, year ect fields

       

      Calendar: DECLARE FIELD DEFINITION TAGGED '$date' Parameters first_month_of_year = 1 Fields

           Year($1) As Year Tagged '$year',

           Month($1) as Month Tagged '$month',

           Date($1) as Date Tagged ('$date', '$day'),

           Week($1) as Week Tagged '$week',

           Weekday($1) as Weekday Tagged '$weekday',

           DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

       

      DERIVE FIELDS FROM FIELDS [Date_Received] USING Calendar;

       

      how do I amend the code / add code above to get financial quarters please?

       

      e.g Q1 = Jan to March

       

      then how do I get financial year ....April 1st 2012 to March 31st of e.g. 2013

       

      Please help

       

      Kind Regards

       

      Joeybird

        • Re: Financial Quarter
          Jonathan Poole

          For the first question , you can add a row to the table as follows. 

           

          'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter'

            • Re: Financial Quarter
              Joanna Seldon

              Hiya

               

              Thank you

               

              it does work for question part 1

               

              however I have a random extra single Q in my drop down menu

               

              Q1,Q2,Q3,Q4 & Q

               

              Kind regards

               

              joeybird x

                • Re: Financial Quarter
                  Ankita Agarwal

                  That is may be because you don't have date infrom of them..

                  And if it is fine then filter out those null values

                    • Re: Financial Quarter
                      Joanna Seldon

                      Hiya

                       

                      makes sense now...

                       

                      understandable.... kool question part 1 completed...

                       

                      can you both please help on how to get part 2 to work please?

                       

                       

                      ..... how do I get financial year ....April 1st 2012 to March 31st of e.g. 2013

                       

                      kind regards

                       

                      joeybird

                        • Re: Financial Quarter
                          Ankita Agarwal

                          Hi

                           

                          Financial Year starting from April would be like this

                          Year($(1),4)

                          • Re: Financial Quarter
                            Jonathan Poole

                            Here is a sample that has a few more fiscal year fields  (FiscalYear, FiscalMonthNumber,FiscalYearMonth , FiscalDayNumberofyear)... you can add as many as you want.

                             

                            If you want to show a bar chart that begins with April,  use  Month as the dimension, but under sort , sort 'by expression' and choos 'fiscalmonthnumber' as the sort .

                             

                             

                             

                            Calendar:

                            DECLARE FIELD DEFINITION TAGGED '$date'

                               Parameters

                                  first_month_of_year = 1,

                                  fiscal_first_month_of_year = 4

                               Fields

                              

                                  Year($1) As Year Tagged '$year',

                               if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear',     

                             

                             

                               Month($1) as Month Tagged '$month',

                                  if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',

                                  if( Month($1)>=4, Year($1), Year($1) -1) & ' - ' & if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalYearMonth tagged '$fiscalyearmonth',    

                                

                                  Date($1) as Date Tagged ('$date', '$day'),

                                  Week($1) as Week Tagged '$week',

                                  Weekday($1) as Weekday Tagged '$weekday',

                                 

                                  DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric'),

                                  DayNumberOfYear($1, fiscal_first_month_of_year) as FiscalDayNumberOfYear Tagged ('$numeric');

                             

                            DERIVE FIELDS FROM FIELDS OrderDate USING Calendar;

                             

                             

                            Capture.PNG

                              • Re: Financial Quarter
                                Joanna Seldon


                                Hiya

                                 

                                this is now working brilliantly, issue I have is I understand how to get the sorting done for fiscal year... issue I have is a person can just want to pick a normal year e.g 2014  ...I would want this to load from jan to dec,

                                 

                                is there a way, if a person picks 'year' it will run jan to dec

                                 

                                but if the person picks 'fiscal year' sorting will dynamically change to apr to mar?

                                 

                                Please x

                                 

                                Kind Regards

                                 

                                Joeybird 

                                  • Re: Financial Quarter
                                    Jonathan Poole

                                    on the 'sort by expression' try the following expression:

                                     

                                    if (getselectedcount([OrderDate.Calendar.FiscalYear])>0, [OrderDate.Calendar.FiscalMonthNumber], [OrderDate.Calendar.Month])

                                      • Re: Financial Quarter
                                        Joanna Seldon

                                        Hiya

                                         

                                        cracked it this is fab!

                                         

                                        thanx

                                         

                                        kind regards

                                         

                                        joeybird

                                          • Re: Financial Quarter
                                            Joanna Seldon

                                            Hiya

                                             

                                            got a major problem, since this code gets added to the sorting, it crashes the app and program.

                                             

                                            it saves and works fine, when you create the dynamic graph. you go out of the app fully and go back in and the moment it goes to the sheet with the graph with the code below, it crashes.

                                             

                                            I have created a new sheet from scratch, test again from scratch 

                                             

                                            you add the code

                                             

                                            if (getselectedcount([OrderDate.Calendar.FiscalYear])>0, [OrderDate.Calendar.FiscalMonthNumber], [OrderDate.Calendar.Month])

                                             

                                            note:OrderDate is changed for my field

                                             

                                             

                                            save ..use sheet all works fine, exit the app and go back in, the moment you get to the sheet with the graph in, it crashes the program

                                             

                                            help please

                                             

                                            kind regards

                                             

                                            joeybird