Help with a YTD formula (see attachments)

Yes, I'm afraid I'm back..  I just can't get this to work out..  I've attached a screen print this time of my report along with a PDF of what the report looks like thus far.

In the GROUP FOOTER 3, under the YTD column (row LY for last year), I am trying to get a sum of the Months that are <= current month but in the previous fiscal year which run from 10/1 to 9/30..  

So at this moment I'd only want to see a sum that would run from 10/1 through current for the previous year.  

Then I would want this to continue automatically going forward without the user needing to put in any dates.  I am just not advanced enough to pull this one off.  I've tried everything I can think of..  Including a formula someone shared with me on an earlier post.
zmyh-rep-cust-edp-fiscal-ytd.pdf
screen.gif
LVL 1
wshcraft70Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wykabryanCommented:
what are the dates based off of? A parameter? When you say "Then I would want this to continue automatically going forward without the user needing to put in any dates." What does this mean?
0
wshcraft70Author Commented:
No the dates are all based on current date..  No parameter..  The formula I've been using to seperate YTD and LY (Last year) is similar to the below.  Some how I need the formula below to summarize OCT
-2 years through March -1 year only..  Then once we reach April I would need the formulat to pick up the sum of Oct -2 years through April -1 year so I'm always getting Last Years sum based on current month.  I think the formula below can get me there somehow as MLMCC pointed out to me in another post but I'm missing something.  This is only grabbing month if = to 1 which is January.  I need it to pick up Oct, Nov, Dec, Jan, Feb, Mar, and then next month add April and so on...

IF Month({oelinhst_sql.billed_dt}) >10 and
 
{oelinhst_sql.billed_dt} IN DateSerial (year(currentdate)-2,10,01)

TO  DateSerial (year(currentdate)-2,Month(currentDate),10)  THEN

(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000)
 
Else

if Month({oelinhst_sql.billed_dt}) <10 and

{oelinhst_sql.billed_dt} IN DateSerial (year(currentdate)-1,01,01)

TO  DateSerial (year(currentdate)-1,Month(currentDate),10) THEN

(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000)
0
wykabryanCommented:
well you could do this is would be a lot more coding but working with fiscal year information is a pain.
you will have to modify the date ranges, but here is what I do for my company's fiscal reporting.

local numbervar x;
local numbervar y:= month(currentdate);

if y = 1 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 2 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 3 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 4 and y in dateserial(year(currentdate)-1,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 5 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 6 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 7 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 8 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 9 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 10 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 11 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 12 and y in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
x
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

wshcraft70Author Commented:
OK, I've tried to run this in Crystal but Y is a numbervar because we are looking for a month but the rest of the statement is comparing against a date range.  So I'm getting an error message that is looking for a number range due to the assignment of Y...  How would I fix this?
0
wykabryanCommented:
oops.. I knew I forgot to update something else.. in the IF portion replace y with {oelinhst_sql.billed_dt}
0
wykabryanCommented:
dont forget for each month you have to figure out what year you want to look at with your date range
0
wshcraft70Author Commented:
OK, that seriously helped.. ;o) Here is what I've got:

local numbervar x;
local numbervar y:= month(currentdate);

if y = 1 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,1,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 2 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,2,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 3 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 4 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-1,10,1) to dateserial(year(currentdate)-1,4,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 5 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,5,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 6 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,6,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 7 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,7,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 8 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,8,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 9 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,9,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 10 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-2,10,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 11 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-2,11,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
if y = 12 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-2,12,31)
then x:=(({oelinhst_sql.qty_to_ship}*{oelinhst_sql.unit_price})/1000);
x


Now, for instance on one of our items, I've got sales in Oct 2008 and Dec 2008 or .2 for each of those 2 months.  However, using the above, what is being returned is .2, I am looking for is to return .4

I THINK I updated the date ranges accordingly but given my level of experience, I wouldn't doubt I've messed it up..  I sure appreciate your help.. Fiscal is going to drive me NUTS!!!
0
wykabryanCommented:
fiscal is always difficult because it requires you to think about Oct as Jan and Sept as Dec.. Doesnt compute correct. Here you will have to mess around with the date ranges to get it exact based on the current month.
0
wshcraft70Author Commented:
Right, so If I'm looking for October through March and current month is = to 3 then I would want Y which is my bill date to look at year -2,10,01 through -1,3,31 RIGHT???  Or am I just totally missing it...  OMG!!  
0
wykabryanCommented:
Oct to March would be the current fiscal year correct? and you want to see last year?
0
wshcraft70Author Commented:
Well, kinda, Fiscal is Oct - Sept and I want to see Last year to Current Month..  So this month I only want to see the total from Oct to March then next month I'd want to see Oct to April and so on...

But hang on, I'm on to something here.. I'll post in a sec. if it works..  Did I mention you're the best!  I now, I'm a KA!!  lol

Thanks,
Nathalie
0
wykabryanCommented:
if month = 3 then
date in date(year(currentdate)-1,10,1) to currentdate

but this will always give you the current year to date..
0
wshcraft70Author Commented:
It is ugly but I think it is working and what did it for me was the light bulb moment when you assigned current month and Numbervar...  I'll see what I come up with on the next steps..  But I can't thank you enough for your time and patience with me!  CHEERS!


local numbervar y:= month(currentdate);

if y = 1 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,1,31)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})else

if y = 2 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,2,29)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})else

if y = 3 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,3,31)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})else

if y = 4 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,4,30)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})else

if y = 5 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,5,31)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})else

if y = 6 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,6,30)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})else

if y = 7 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,7,31)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})+({@LY 07 JUL INVOICED})else

if y = 8 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,8,31)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})+({@LY 07 JUL INVOICED})+({@LY 08 AUG INVOICED})else

if y = 9 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,9,30)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})+({@LY 07 JUL INVOICED})+({@LY 08 AUG INVOICED})+({@LY 09 SEP INVOICED})else

if y = 10 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,10,31)
    then ({@LY 10 OCT INVOICED})else

if y = 11 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,11,30)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})else

if y = 12 and {oelinhst_sql.billed_dt} in dateserial(year(currentdate)-2,10,1) to dateserial(year(currentdate)-1,12,31)
    then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})

0
wshcraft70Author Commented:
THANK YOU!!!!  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.