10 Replies Latest reply: Nov 12, 2011 8:00 AM by Steven Blower RSS

    selected financial year to date vs the previous financial year to date

      Hello all,

      I am hoping that someone can help me with this please,

      I am trying to create monthly sales reports that have six columns based on the selection of year and month

      month to date |  budget month to date | last years month to date  ||   Year to date | budget year to date | Last years year to date 

      I am struggling with the formula for the two columns that should show the last years month and year - as you can see i have set the last year as 2010-2011 which works if for the current management results 2011-2012 vs 2010-2011.

      However if i wish to see results for 2010-2011 vs 2009-2010 I get 2010-2011 vs 2010-2011.

       

      so far in the pivot table i have the following expression......... 

      // Month last year 
      
      if(MgmtReportHdrID='Month LY',sum({$<SalesTypeKey = {'C'},YEARFYID = {'2010-2011'}>} CreditsLineSale)
      +sum({$<SalesTypeKey = {'I'},YEARFYID = {'2010-2011'} >} InvoiceLineSale),
      
      // Year to date last year 
      
      if(MgmtReportHdrID='LYTD',sum({$<SalesTypeKey = {'C'},YEARFYID = {'2010-2011'},MONTHNAMEID =  {"<=$(=max(MONTHNAMEID))"}>} CreditsLineSale)
       +sum({$<SalesTypeKey = {'I'},YEARFYID = {'2010-2011'},MONTHNAMEID =  {"<=$(=max(MONTHNAMEID))"}>} InvoiceLineSale))
      
      
      

       

      What I am trying to do is get the previous financial years totals based off the financial year that has been selected

      After looking around the forum and have come across YEARFYID = {"$(=only(YEARFYID) - 1 )"} which i tried but cannot get the formula to work. it does not show no any results/data.

       

      Thank you  in advance

      Tara

        • selected financial year to date vs the previous financial year to date
          Kamal Naithani

          Hi Tara........I think this will help you...

          YEARFYID = {"$(=only(YEARFYID) - 1 )"} ,.....by using this you will get the value that is currently included ie 2011-2012 vs 2010-2011..

          So pass the blank value to get the desired result that you want to get..

          YEARFYID = {$(=only(YEARFYID) - 1 ),Year=>}

            • selected financial year to date vs the previous financial year to date

              Hi thank you for that,

               

              I tried the formula again without the -1 

              YEARFYID = {"$(=only(YEARFYID) )"} and it does work to shows the year I have selected (it did not before but i think that may have been something I did elsewhere in the script).

               

              However when I try it with the -1 I get a column of zeros

              =   Sum({$<YEARFYID = {"$(=only(YEARFYID) -1 )"} >}  InvoiceLineSale)   +  Sum ({$<YEARFYID = {"$(=only(YEARFYID) -1 )"} >} CreditsLineSale)

               

              I also tried your sugestion below (changing Year = to YEARFYID= )

              So pass the blank value to get the desired result that you want to get..

              YEARFYID = {$(=only(YEARFYID) - 1 ),Year=>}

               

              using

              =   Sum({$<YEARFYID = {$(=only(YEARFYID) - 1 ) ,    YEARFYID = >}        InvoiceLineSale)   +  Sum ({$<YEARFYID = {$(=only(YEARFYID) - 1 ),YEARFYID =>} CreditsLineSale)

              this did not work (with a column of  null results) there seems to be an issue using the = after the YEARFYID

               

              I appriciate your response and hope you can me help further....

              Tara

            • selected financial year to date vs the previous financial year to date

              can you tell me the starting and ending month of your financial year after then only i can help you.

               

              regards

              vijit

              • Re: selected financial year to date vs the previous financial year to date

                Hi Tara

                 

                I managed to solve this one for a report I was creating.

                 

                If I have understood correctly I believe you wish to have a table whereby if you select a value of financial year then one column of the table will populate with the sum of year to date sales for the year you have selected and another column will populate with the year to date sales for the previous year.

                 

                So, selecting only one year will liberate results for both years in your table to enable a comparison to be made.

                 

                 

                To achieve this you need two set analysis expressions:

                 

                 

                First: Sales for current financial year

                 

                     Sum({$<YEARFYID={$(=Only(YEARFYID))}>}  CreditsLineSale)

                     +

                     Sum({$<YEARFYID={$(=Only(YEARFYID))}>}  InvoiceLineSale)

                 

                 

                Second: Sales for previous financial year

                 

                     Sum({$<YEARFYID={$(=Only(YEARFYID)-1)}>}  CreditsLineSale)

                     +

                     Sum({$<YEARFYID={$(=Only(YEARFYID)-1)}>}  InvoiceLineSale)

                 

                 

                The first expression sums the value of sales for the financial year you have selected.  The second does the same thing but it subtracts 1 from the financial year value you have chosen and so sums the value of sales for the previous financial year.

                 

                If you use a text-based value of financial year such as '2010-2011' the expression cannot evaluate the subtraction and so offers no results.  Also if you select more than one year QV does not know from which of them you wish to subtract the 1 and so offers no results.

                 

                So in order for this to work there are two conditions that need to be met:

                 

                1: Your financial year values must be numeric

                • You can set this in your load script

                           e.g. If (Month(SalesDate)<=3,Year(SalesDate),Year(SalesDate)+1) as YEARFYID

                 

                This example will give you a numeric value for financial year in a field called YEARFYID, the value being the year at the end of the financial year

                E.G. For all data rows with a sales date of April to December of 2011 or January to March of 2012 the value of YEARFYID would be 2012

                 

                2: You must select only one value of 'Financial Year' in a list box or chart.

                 

                 

                So in summary to achieve your desired result the steps are:

                 

                1: In your load script set a 'FinancialYear' field to hold numeric values e.g. YEARFYID

                2: Create your table

                3: Add your required dimensions to your table           e.g. Customers

                4: Add the first set expression to your table chart

                 

                     Sum({$<YEARFYID={$(=Only(YEARFYID))}>}  CreditsLineSale)

                     +

                     Sum({$<YEARFYID={$(=Only(YEARFYID))}>}  InvoiceLineSale)

                 

                5: Add the second set expression to your table chart

                 

                     Sum({$<YEARFYID={$(=Only(YEARFYID)-1)}>}  CreditsLineSale)

                     +

                     Sum({$<YEARFYID={$(=Only(YEARFYID)-1)}>}  InvoiceLineSale)

                 

                6: Add a 'Financial Year' list box to display values of YEARFYID

                7: Reload your data

                8: Select one value only from your 'Financial Year' list box

                 

                The table chart should show both your current year and previous year results.

                 

                Hope that helps.

                 

                Kind regards

                 

                Steve

                • selected financial year to date vs the previous financial year to date

                  flags:

                   

                  inyeartodate(D, $(vToday), 0) * -1 AS CYTD                               //Current year-to-date

                  inyeartodate(D, $(vToday), -1) * -1 AS FPYTD                            //First prior year-to-date

                   

                  make the above flags in your script.

                  in D substitute 1 april 2011, so that you get the flags for current year and previous year till date.

                   

                  expression:

                  =sum( InvoiceLineSale*CYTD)*-1                               // for current year till date

                  =sum( InvoiceLineSale*FPYTD )*-1                            // for previous year till date

                   

                  apply the above expression in the bar chart.

                   

                  try it out, it will definitely work.

                   

                  regards

                  • selected financial year to date vs the previous financial year to date

                    as your financial year is from april to march , so put the below expression in your script:

                     

                    if(wildmatch(month(Date),'Jan','Feb','Mar'),year(Date)-1,year(Date)) as Year

                     

                    regards