fiscal year calc

[Cateleb]

Hi, I've searched through the forums but can't find what I need, which is a calc to filter a portal to only show records for the current fiscal year, but as I'm in OZ that's 1 July to 30 June 

 

I started a long winded calc using if(month(date) =>7 and month(date) <= 12; etc etc etc

 

but I thought there should be a more 'elegant' way, possibly using the weekoffiscalyear function .. BUT .. it thinks everyone is American and for whatever reason isn't interested in the system settings  

 

any suggestions ?

 

cheers

Cate

[comment]

Try =

Let ( [
today = Get ( CurrentDate ) ;
today.fy = Year ( today ) + ( Month ( today ) ≥ 7 ) ;
child.fy = Year ( Child::Datefield ) + ( Month ( Child::Datefield ) ≥ 7 )
] ;
today.fy = child.fy
)

the weekoffiscalyear function .. BUT .. it thinks everyone is American and for whatever reason isn't interested in the system settings

 

I don't think that is a correct assessment. It's true that the WeekOfYearFiscal () function ignores the system settings, though. There are no system settings regarding the start of a fiscal year.

[Cateleb]

thanks comment .. I'll give that a go

 

I might put in a request that the weekoffiscalyear function has another parameter for the start of the fiscal year .. I think England has theirs ending 30 Sept

[Cateleb]

yep, works like a treat thanks Comment  ... I'll now just work out why :-)

[comment]

I might put in a request that the weekoffiscalyear function has another parameter for the start of the fiscal year ..

 

It wouldn't help you in the current case if it did.

 

I think England has theirs ending 30 Sept

 

I am not sure that's the case:

http://en.wikipedia.org/wiki/Fiscal_year#Operation_in_various_countries.2Fregion

 

Note that these are (mostly) government fiscal years. In many jurisdictions, companies are allowed to start their fiscal year on any date they choose.

[Cateleb]

OK, thanks Comment.

 

the portal lists the kms travelled per job and I've done a running total calc which displays in the portal, and NOW that the portal is filtered for the current fiscal year we can check the total kms (we need to pay a different tax once it exceeds 5,000 kms per fiscal year per employee).  The only other prob is there'll be maybe 100 records in a fiscal year and checking the running total means scrolling to the last (bottom) record .. sorting doesn't help as the running total is still the last record  .. obviously ideally I'd like the total at the top, but if it can't then I guess I need a field outside the portal with a fiscal calc in the kms table  ?

 

regards

Cate

[comment]

Do you really need to show a running total inside the portal? If not, you could probably change the field to total and place it outside the current portal, in a one-row filtered portal of its own. However...

 

IMPORTANT:

Portal filtering works at layout level only. Calculations work at data level, with no regard to what is or isn't on any layout. Passing filtered data to a calculation is awkward and generally not good practice - because modifying a layout is not supposed to be able to break a calculation.

 

In addition, portal filtering is slow. It needs to check every related record for the existence of the filtering condition/s - as opposed to a relationship that can take advantage of the index. If you have 100 records that pass the test (out of how many?) you are already close to the limit of usefulness of portal filtering and should consider moving to a dedicated relationship.

 

Another option you should consider is producing a report from the portal table directly, after finding the relevant records only.

[Cateleb]

geeez, of course !!  a total on a 1 row portal is fine ..  sometime you start down a path and get tunnel vision and forget what the original purpose was

 

thanks for the other info as well  .. .there may well be over 1000 records, but only 100 for any particular financial year .. it's not something we need to look at regularly, it's just something to keep an eye on twoards the later part of the FY,  the portal is on a tab so it's not in focus during the normal work     

 

Thanks again

[comment]

there may well be over 1000 records, but only 100 for any particular financial year

 

Just keep in mind that the filter has to go over all of the >1,000 in order to pass the 100 (and reject the other >900). And it does this on every screen refresh.

[Cateleb]

Ok, I'll see how it goes, works fine with the records we've currently got but we'll see what happens in a couple of years ..  if I need to go the relationship route, how is that done .. with a self join ?

[comment]

if I need to go the relationship route, how is that done .. with a self join ?

 

No, you would define an unstored calculation field cCurrFY (result is Number) in the parent (Jobs?) table =

Let ( 
today = Get ( CurrentDate ) 
;
Year ( today ) + ( Month ( today ) ≥ 7 ) 
)

and another calculation field cFY (stored, result is Number) in the child (Trips?) table =

Year ( Datefield ) + ( Month ( Datefield ) ≥ 7 )

Then create a relationship between Jobs and a new occurrence of the Trips table as:

Jobs::JobID = Trips 2::JobID
and
Jobs::cCurrFY = Trips 2::cFY

Now you can place a summary field from Trips 2 directly on the layout of Jobs and it will show the summary value for current FY trips only.

 

In addition, you can use the expression =

Sum ( Trips 2::Mileage )

in a calculation performed in the context of Jobs to refer to the total mileage traveled in the current fiscal year. This is especially useful if you need to process the result further (e.g. divide it by the number of employees).

[Cateleb]

great,  thanks again Comment ,