Fiscal Year to Date 3

[Fsyeda]

Hello everyone!

I am comparing Sales from Last Year Fiscal YeartoDate [EX10/01/2011) - 8/16/2012] with Current Fiscal YeartoDate [Ex:10/01/2012 - 08/16/2013]
Fiscal starts from October.

Below is the Record selection that I am using:

{Table.InvoiceDate} in [{@LastFiscalYear_From} to {@LastFiscalYear_To }]
or
{Table.InvoiceDate} in [{@ThisFiscalYear_From } to {@ThisFiscalYear_To}]

Where;

LastFiscalYear_From:
If ({Table.Fiscalyear}) = Year(currentdate)-1
then
Date(Year(CurrentDate)-2, 10, 01)


LastFiscalYear_To :
If {Table.Fiscalyear} = Year(Currentdate) - 1
then
Date(Year(Currentdate)-1, Month(Currentdate), Day(Currentdate))


ThisFiscalYear_From :
If { Table.fiscalyear} = Year(Currentdate)
then
Date(Year(Currentdate)-1, 10, 1)


ThisFiscalYear_To :
CurrentDate


SalesThisFiscalYear :
If {Table.Fiscalyear} = Year(Currentdate)
then {Table.sales}


SalesLastfiscalYear :
If {Table.Fiscalyear} = Year(Currentdate) - 1
then {Table.sales}


The problem is; sales for this Fiscalyear match but the last Fiscalyear sales does not.

There is something very silly that I am missing. I have spent hours on this but could not figure out. If someone can help.

 

[Kim296]

I'm sorry that I don't know how to help you fix your problem, but I read an article about this yesterday. Maybe this will help you.

http://kenhamady.com/cru/archives/56

 

[Fsyeda]

Unfortunately, it doesn't help with my issue.

 

[dunlop1975]

Have tried just hard coding the date value to make sure you are actually getting the proper numbers for the previous year?

i.e change {Table.InvoiceDate} in [{@ThisFiscalYear_From } to {@ThisFiscalYear_To}]
to

{Table.InvoiceDate} in [date(2011,10,01) to date(2012,08,16)]

That will at least help point to where the issue is

 

[Fsyeda]

I don't want it to be hardcoded , I wanted it to be something I can use in future too.

I fixed it finally, and the solution is this

If ({Table.Fiscalyear} = Year(Currentdate) - 1
and {Table.InvoiceDate} <= Date(Year(Currentdate)-1,Month(Currentdate),Day(Currentdate)))
then {Table.sales}

It was pulling in the Sales of Fiscal2012 completely, rather than to the date.

But I still have a question, though I had my record selection set to those dates , why did it not apply to the whole report? Am I incorrectly doing the record selection or record selection does not apply to formula fields.If yes, then what is the purpose of the record selection?

 

[AndyMc]

dunlop1975 was not suggesting you permanently hard code the dates, but to do it as an intermediate step to trouble-shoot your issue. This would help you determime if the problem was with the date formulas or somewhere else.

 

[pmax9999]

The Record Selection limts the record set that Crystal Report needs to work with. In theory you could leave the Record Selection blank and extract every record from the database, and have Crystal suppress or ignore the records you don't need. It is much more efficient to use Record Selection to return just the records you need.

My approach to your date formulas would be as follows:

[Code {@LastFiscalYear_From}]
If Month(CurrentDate) >= 10
Then Date(Year(CurrentDate) - 1, 10,1)
Else Date(Year(CurrentDate) - 2, 10,1)
[/Code]


[Code {@LastFiscalYear_To}]
DateAdd("yyyy", -1, CurrentDate)
[/Code]

[Code {@ThisFiscalYear_From}]
If Month(CurrentDate) >= 10
Then Date(Year(CurrentDate), 10,1)
Else Date(Year(CurrentDate) - 1, 10,1))
[/Code]

[Code {@ThisFiscalYear_To}]
CurrentDate
[/Code]

Use these formulas in the Record Selection to limit the records to just those you need.

Then reference these formulas in your 2 Sales formula to set the date ranges.

Hope this helps

Pete

 

[Fsyeda]

I am sorry to have skipped reading the suggestion from Dunlop in detail. I guess I was too excited to write the solution down.

Pete: I am going to try your suggestion and let you know if it works fine.

 

[Fsyeda]

I did apply the suggested formulas Pete, and they work for the report ! I did actually realize that the earlier formulas would have been a problem in the months of OCT, NOV, and DEC. This solution makes everything simple and accurate.

The sales formulas are as follows:

SalesThisFiscalYear :
If {table.InvoiceDate} in [{@ThisFiscalYear_From} to {@ThisFiscalYear_To}]
then {table.Sales}


SalesLastfiscalYear :
If {table.InvoiceDate} in [{@LastFiscalYear_From} to {@LastFiscalYear_To}]
then {table.Sales}