YTD revenue formula

[ghmd47]

my table have these data field Account, posting_date, fiscal_year and Tran_amount. I would like to create a calculation for revenue base on these field, but I can't get the YTD amount for Revenue. Here is my formula:
if({GLTran.Account} >=40010 and {GLTran.Account}<=44610) then {GLTran.Tran_Amount}
Base on this formula I can't get the YTD revenue amount. Please show me how to get the YTD revenue amount. Thanks

 

[lbass]

By YTD do you mean Fiscal Year to Date? If so, what is your fiscal year? Is it named based on the ending year? Is posting_date a date type field? Is fiscal_year a number field? If these are strings, please show how they are displayed.

-LB

 

[ghmd47]

YTD by posting_date. Posting_date is a date type field and fiscal_year is a number field.
I created two formula for revenue:
first is current_year:
if month({GLTRANS.POSTING-DATE})>=12
then Year ({GLTRANS.POSTING-DATE}) + 1
Else Year ({GLTRANS.POSTING-DATE})

the second is YTD-Revenue:
if {GLTRANS.FISCAL-YEAR}={@CurrYear}
and ({GLTRANS.ACCOUNT}>=40010 and {GLTRANS.ACCOUNT}<=44610) then {GLTRANS.TRAN-AMOUNT} *-1

is it right? is it something missing on this formula?
Thanks,

 

[lbass]

I meant what dates does your fiscal year run, e.g., is it July 1 to June 30? April 1 to March 31?

Your formula won't work as months are never greater than 12.

-LB

 

[ghmd47]

I think it run from January 1 to December 31. But I need to check with our accounting. Do you know how to write this formula. I can't get 2010 YTD. I just saw year 2006 base on this formula. But i want to get 2010 YTD. I am sure my data have fiscal year 2010. Please help me!!!

Tks,

 

[lbass]

If your fiscal year matches the calendar year, then you can use:

if({GLTran.Account} >=40010 and
{GLTran.Account}<=44610) and
{table.date} in lastyearYTD then
{GLTran.Tran_Amount}

For the current year, you could change the date criteria to:

{table.date} in YearToDate

-LB

 

[ghmd47]

Thanks! I will tried this formula.