
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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=>}

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you tell me the starting and ending month of your financial year after then only i can help you.
regards
vijit

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My financial year is from the 1st April through to the 31st March
Regards
Tara

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
= 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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
-
- 1
- 2
- Next Replies »