| Author |
Message |
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
  
Topic: Fiscal Year To Date Formula
Posted: 03 Dec 2012 at 11:52am |
|
Hey all,
I am new to both Crystal reports, and report writing in general, so bare with me if I seem a bit naive. I have been asked to wright a report that will show a select group of customers targeted sales for the time period that covers a Fiscal year to date of October 2011-Sept 2012 then the same for the 2012-2013 time period. I have been very unsuccessful in finding a formula that works. the closest I have found is along these lines...
if Month ( {Transaction.Date} ) >= 10
then Year ( {Transaction.Date} ) + 1
else Year ( {Transaction.Date} )
But I don't seem to be getting the results I am looking for. Can some one please let me know what I'm missing, or if you have a formula you know will work and are willing to share. Thank you so much for the help!
|
 IP Logged |
|
|
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 04 Dec 2012 at 2:56am |
|
what version of Crystal are you using? 2008 I think has a Yeartodate function.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Dec 2012 at 4:03am |
also can you explain a little more what you are trying to accomplish here?
I am not sure if you want all data from last fy or just last fy to date? and then how are you using this data in the report?
|
| IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 04 Dec 2012 at 4:11am |
|
Thank you for the quick responses, and sorry for the confusion, i am using crystal 11, and it does have a YTD but no fiscal YTD options.
I am trying to create a report that will show the fiscal year to date sales total for customers for the time period of October 2011 to Sept. 2012, then the same numbers for 2012-2013, so they can compare the sales total, to track the progress of their sales.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Dec 2012 at 4:41am |
maybe this?
{Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 2 else 1),10,1) to dateadd('yyyy',-(if month(currentdate)<10 then 2 else 1),currentdate)
or
{Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 1 else 0),10,1) to currentdate
|
| IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 04 Dec 2012 at 4:43am |
Originally posted by DBlankmaybe this?
{Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 2 else 1),10,1) to dateadd('yyyy',-(if month(currentdate)<10 then 2 else 1),currentdate)
or
{Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 1 else 0),10,1) to currentdate I'll give it a try and let you know in a little while if it works out. Thank you so much for the help! 
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 04 Dec 2012 at 7:40am |
Originally posted by MellieD
Hey all,I am new to both Crystal reports, and report writing in general, so bare with me if I seem a bit naive. I have been asked to wright a report that will show a select group of customers targeted sales for the time period that covers a Fiscal year to date of October 2011-Sept 2012 then the same for the 2012-2013 time period. I have been very unsuccessful in finding a formula that works. the closest I have found is along these lines... if Month ( {Transaction.Date} ) >= 10
then Year ( {Transaction.Date} ) + 1
else Year ( {Transaction.Date} )But I don't seem to be getting the results I am looking for. Can some one please let me know what I'm missing, or if you have a formula you know will work and are willing to share. Thank you so much for the help!
Ken Hamandy is usually a great source. But then again, he is only gonna give you enough information (if your a beginner to get your interest). He wants you to buy the answers :)
http://kenhamady.com/cru/archives/56
|
| IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 04 Dec 2012 at 12:03pm |
Originally posted by comatt1 Originally posted by MellieD
Hey all,I am new to both Crystal reports, and report writing in general, so bare with me if I seem a bit naive. I have been asked to wright a report that will show a select group of customers targeted sales for the time period that covers a Fiscal year to date of October 2011-Sept 2012 then the same for the 2012-2013 time period. I have been very unsuccessful in finding a formula that works. the closest I have found is along these lines... if Month ( {Transaction.Date} ) >= 10
then Year ( {Transaction.Date} ) + 1
else Year ( {Transaction.Date} )But I don't seem to be getting the results I am looking for. Can some one please let me know what I'm missing, or if you have a formula you know will work and are willing to share. Thank you so much for the help!
Ken Hamandy is usually a great source. But then again, he is only gonna give you enough information (if your a beginner to get your interest). He wants you to buy the answers :)
http://kenhamady.com/cru/archives/56 Yeah, I contacted him first, and he talked about pricing right out the doorway, i really want to try and figure this out with out having to spend more money... I'm learning quickly, but not as quickly as I would have liked. Thank's for the info...
|
| IP Logged |
|
MellieD
Newbie
Joined: 03 Dec 2012
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 04 Dec 2012 at 12:11pm |
Originally posted by DBlankmaybe this?
{Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 2 else 1),10,1) to dateadd('yyyy',-(if month(currentdate)<10 then 2 else 1),currentdate)
or
{Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 1 else 0),10,1) to currentdate So... i'm not sure if it's my lack of knowledge/ skills or not, but the formula is not giving me the results i was looking for, instead of returning the sales total, it is showing "False" as it's response. Let me give you more details on what I have so far... The report is grouped by customer name, and each customer has 3 columns, 1st: Fiscal Year To Date Sales from October 2011- September 2012 summed 2nd: Fiscal Year To Date Sales from October 2012- September 2013 summed 3rd: the difference between the two numbers. I currently have just the YTD formulas with the standard 12 month period, but have been asked to change it to the Fiscal year mentioned above. Any other suggestions? Or am i just not understanding the formulas? Sorry i seem so lost, and thank you both for the help. 
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Dec 2012 at 1:06pm |
|
Sorry, my formula was to be used in the select expert to limit your data to only rows that would be last fy to date and this fy to date. Hence the returning of true or false.
If you want to get specific values the easiest way is to make two formulas out of either side of the or statement.
//last fy
If {Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 2 else 1),10,1) to dateadd('yyyy',-(if month(currentdate)<10 then 2 else 1),currentdate) then table.amount else 0
Now sum this to get your total
Sum(@last fy)
//this fy
If {Transaction.Date} in date(year(currentdate)-(if month(currentdate)<10 then 1 else 0),10,1) to currentdate then table.amount else 0
Now sum this to get your other total
Sum(@this fy)
|
| IP Logged |
|
|
|
