| Author |
Message |
kreusch
Newbie
Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
|
   Topic: last fiscal year
Posted: 06 Feb 2014 at 9:13am |
|
Hi,
I am using the formula below to pull my last fiscal year totals. our fiscal year starts in may. The formula was working great as of Dec 2013 but when we run it as of Jan 2014 I am not getting any data. Any insight is appreciated.
//Last FY
If {GLIncomeStatementStage1.GLTransactionDate}in date (year ({GLIncomeStatementStage1.SelectDate})-(if month ({GLIncomeStatementStage1.SelectDate})<5
then 2 else 1),5,1)
to dateadd ('YYYY', - (if month ({GLIncomeStatementStage1.SelectDate})<5 then 2 else 1), {GLIncomeStatementStage1.SelectDate})
then {GLIncomeStatementStage1.GLTransactionDebitAmount}
else 0
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 06 Feb 2014 at 12:12pm |
|
Is the determination of the "current fiscal year" based on the current date or on some date parameter?
I would break this down into multiple formulas to make it easier to track what's happening and where you might be seeing ann an error... So, assuming that the FY starts on May 1st, the start and end dates of the current fiscal year would be this:
Current FY Start Date
if Month(currentdate) >= 5 then Date(Year(currentdate), 5, 1) else Date(Year(currentdate) - 1, 5, 1)
Current FY End Date
if Month(currentdate) >= 5 then Date(Year(currentdate) + 1, 4, 30) else Date(Year(currentdate), 4, 30)
If the calculation has to be based on the SelectDate field, then replace "currentdate" with "{GLIncomeStatementStage1.SelectDate}" in these first two formulas.
Your prior year start and end dates would then look like this:
Prior Start Date
DateAdd('yyyy', -1, {@Current FY Start Date})
Prior End Date
DateAdd('yyyy', -1, {@Current FY End Date})
You would then re-write the final formula to something like this:
If {GLIncomeStatementStage1.GLTransactionDate} in {@Prior Start Date} to {@Prior End Date} then {GLIncomeStatementStage1.GLTransactionDebitAmount}
else 0
By breaking it down this way, you can temporarily add any or all of the date formulas to your report to see what the values are for debugging purposes.
-Dell
|
|
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 04 Apr 2017 at 2:57am |
|
Date(Year(currentdate), 5, 1)
what is 'Date' mean here?
I tried it in sql and it can not recognize 'date'
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 04 Apr 2017 at 4:14am |
|
That is a Crystal function, not a sql function. It converts the year, month, day parameters to a date.
What type of database are you connecting to? I may be able to get you the correct syntax, but each type of database is different.
-Dell
|
|
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 05 Apr 2017 at 3:31am |
|
Thanks, Dell.
I am trying to set up crystal report to pull fiscal year (from October 1 last year to 9/30 next year). I think the most easiest way put @fiscalYearBegin and @FiscalYearEnd in sql WHERE clause in below sample sql code:
select
HSPC.LEVEL_OF_CARE,
Case when FINANCIAL_CLASS_NAME = 'Medicare' Then 'Medicare'
when FINANCIAL_CLASS_NAME = 'Medicaid' Then 'Medicaid'
ELSE 'Other' End as FINANCIAL_CLASS_NAME,
COUNT(HSPC.TX_ID) as DaysLevCare,
--HSPC.CALENDAR_DATE,as
convert(varchar, HSPC.CALENDAR_DATE, 103) as CALENDAR_DATE,
HSP.Serv_Area_ID as ServiceArea,
LOC.LOC_ID
From
V_HSPC_COST_REPORT_DATA HSPC inner join HSP_TRANSACTIONS HSP
on HSPC.TX_ID = HSP.TX_ID LEFT Join CLARITY_LOC LOC
ON HSP.FACILITY_ID = LOC.LOC_ID
where
(HSPC.CALENDAR_DATE >= {?FiscalYearBegin} and HSPC.CALENDAR_DATE < {?FiscalYearEnd} )
and HSP.DEPARTMENT = '1001501001' ---Hospice department Jim Meng
and ((0 IN {?Service Area}) OR HSP.SERV_AREA_ID in {?Service Area})
group by
Case when FINANCIAL_CLASS_NAME = 'Medicare' Then 'Medicare'
when FINANCIAL_CLASS_NAME = 'Medicaid' Then 'Medicaid'
ELSE 'Other' End
,HSPC.LEVEL_OF_CARE
,HSPC.CALENDAR_DATE
,HSP.Serv_Area_ID
,LOC.LOC_ID
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 05 Apr 2017 at 3:46am |
|
Dell,
Any suggestion?
This report is yearly run report and should be scheduled.
how can I set up FiscalyearBegin and FiscalYearEnd? in sql or in crystal report?
Thanks.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 05 Apr 2017 at 4:15am |
|
Will this always be the prior fiscal year based on the current date or could it be the current fiscal year based on the current date?
Also, what type of database are you connecting to? Oracle? SQL Server? Something else?
-Dell
|
|
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 05 Apr 2017 at 5:31am |
|
connect to SQL database server.
fiscal year may vary for each report.
Mine should be from '10/1/last year' to '9/30/next year'. total 12 months: 10/11/12 months from previous year, then include 01/02/03/04/05/06/07/08/09 months.
Not sure I answer your question.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 05 Apr 2017 at 6:08am |
|
So this will always show the prior fiscal[/] year which actually contains months 10, 11, and 12 of the prior calendar year and months 1-9 of the current calendar year. Is that correct?
-Dell
|
|
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 05 Apr 2017 at 6:14am |
|
exactly
|
|
IP Logged |
|
|
|
