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

[#1]

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

[kamalqlik]

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=>}

[#3]

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

regards

vijit

[#4]

My financial year is from the 1st April through to the 31st March

Regards

Tara

[#5]

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

[#6 · Accepted solution]

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

[kamalqlik]

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

Hi Tara.....try usisng this...May be this may work.....

regards

Kamal

[#8]

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

[#9]

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

[#10]

Thankyou Steve...

This little formula has been troubling me for quite sometime now ... you have made my week.

Your explaination was very easy to follow and at your suggestion I changed my financial year load script

from txt based  

YearName(InvoiceDateID,0,$(vStartMonthFY))                   AS YEARFYID, 

to numeric

If (Month(InvoiceDateID)<=3,Year(InvoiceDateID),Year(InvoiceDateID)+1)              AS YEARFYID,

After that all my expressions worked as they should

Again thankyou very much I appriciate the time you took to respond

regards

Tara