Fiscal Date/Text combination

[LaRetta]

Hi everyone,

Although I refer to Custom Functions, these can be regular calculations so I decided to post here instead. They are:

YearMonth. This produces 200505 and is used for sorting and MANY columnar month portal relationships.

Year(date) & Right( "0" & Month(date); 2 )

Fiscal. I've been using Fiscal on reports as merge "Fiscal 2004" but it is now confusing to some staff since it's 2005. They see 2004 and think it's from last fiscal. It is also used in some of my joins. gFiscalStart is global number (from host) indicating the start of our Fiscal; in our case 10. It is called num in the CF.

Year(datefield) - (Month(datefield) < gFiscalStart)

They requested 04-05 so there wouldn't be confusion. I created ANOTHER CF (FiscalPeriod) of:

Right(Fiscal(datefield; num); 2) & "-" & Right(Fiscal(datefield; num) + 1; 2)

But I stopped myself. I feel like I'm converting only to convert again. Make sense? I've read calculations which refer to other calculations takes more resources and instead should be fully written out but I was unsure about CFs. I know I've seen one CF refer to another but THIS makes me feel like I'm spinning my wheels because I'm again referencing the same datefield; num combination.

Thank God these are CFs - it would be a mess changing the calculations in every table they effect. Do others deal with these kinds of problems too? How would you write the FiscalPeriod properly and do you have suggestions on providing the most flexibility in case they want to change something again?

Thank you!

LaRetta

[LaRetta]

Okay, maybe this was missed by most ... but 25 people read it and no response? Was my question too unspecific or confusing?

Another example of my fiscal vs. calendar problem ... people want a radio button for Fiscal and Calendar. Some reports should show fiscal year totals, some calendar year. I'm attempting to create a standardized system for relating and displaying appropriately. I was hoping for ideas to provide this standardization (via CF), plus how best to allow multiple parameters so that I/Management/Users can specify (via global) the formatting (and thus the resulting relationships) to allow flexibility.

Am I the only one with these problems? Maybe my thinking is out of whack - it won't be the first time.

LaRetta

[-Queue-]

If I read your post correctly, you want to simplify

Right(Fiscal(datefield; num); 2) & "-" & Right(Fiscal(datefield; num) + 1; 2)

If so, I would use

Let( F = Fiscal( datefield; num );

Right( F; 2 ) & "-" & Right( F + 1; 2)

)

Or is it more complicated than that?