Date Style Multiline

[LaRetta]

In Invoices or LineItems, instead of creating separate calcs (or Auto-Enter Replace) of Day Of Week, Week Of Year, Month, Caledar Year, Fiscal Year, etc., I think I can use ONE multikey which will fit all my needs. I need these date calcs for relationships (dynamic columnar portals). Can I fine-tune this text calc please?

// Date style multiline

Let (

[

d = Date ( Month ( ShipDate ) ; Day ( ShipDate ) ; Year ( ShipDate ) ) ;

m = Month ( d ) ;

y = Year ( d )

] ;

DayName ( d ) & ¶ &

WeekOfYear ( d ) & ¶ &

MonthName ( d ) & ¶ &

y & ¶ &

Right ( y - ( m < 10 ) ; 2 ) & "-" & Right ( y + 1 ; 2 ) & ¶ &

"Q" &

Case(

m = 10 or m = 11 or m = 12 ; 1;

m = 1 or m = 2 or m = 3 ; 2 ;

m = 4 or m = 5 or m = 6 ; 3 ;

4)

)

Our Fiscal starts 10/1. I considered Mod() for tens and Div() for units with Choose() with tens as the exception but I got myself twisted. And please FileMaker make the darned calc boxes bigger!!

LaRetta

[comment]

Question: do you really need to aggregate all Monday records? Of any Monday?

And a similar question about months, weeks of year and fiscal quarters: regardless of the year?

[LaRetta]

Oh! I see what you mean! I'd get every Monday in my entire table summ'd! I had planned to include the year (global pop-up) to use in the calc which generated the joins ... but I'm going another route now ... much better idea! Thank you! ;)

[LaRetta]

Okay, Michael, taking your lead ... I wanted to generate only dates in a multiline (the first date of each group, looking backwards to find each beginning). I kept thinking that text dates were undependable (system date type stuff) and I should be using number dates. But here's what I've come up with. I KNOW it can be much better! Is this what you mean on the Date Key though? Scheduled Ship Date is required and validated.

Let(

[

base = If( PhysicalShipDate; PhysicalShipDate; ScheduledShipDate) ;

D = Day(base) ;

M = Month(base) ;

Y = Year(base)

] ;

// day is the same as date. No need to look backwards

"D " & base & ¶ &

// week - look back to first Sunday

"W " & base - DayOfWeek(base) + 1 & ¶ &

// month - look back to 1st

"M " & base - D + 1 & ¶ &

// quarter - look back to start Jan, Apr, Jul or Oct

"Q " & Date ( M - Mod ( M - 1 ; 3 ) ; 1 ; Y ) & ¶ &

// calendar year - look back to 1/1/(always same year)

"C " & Date ( 1 ; 1 ; Y ) & ¶ &

// fiscal year - look back to 10/1 (of prior year if necessary)

"F " & Date ( 10 ; 1 ; Y - ( M < 10 ) )

)

Ooops! Forgot Fiscal Year. But I can specify that within the left-side calc, right? Also difference between fiscal quarter and calendar quarter, I guess.

[color:red]Above calculation modified after (multiple) input from Comment below.

[comment]

That looks good. I would make the quarter:

Date ( M - Mod ( M - 1 ; 3 ) ; 1 ; Y )

or a simple Choose() would also work well here. It might look inelegant to spell out 12 months, but from the computing POV it's very efficient.

I think you need the fiscals on the right-side. Otherwise you will have to mess with multi-lines on the left side as well.

And yes, you should turn the dates into numbers, but only after you've done testing. You need to see dates in order to troubleshoot.

Now for the left side, try an intermediate repeating field cAnchorDatesR (result is Date) =

Let ( [

d = Extend ( gDate ) ;

mode = Extend ( gMode ) ;

i = Get ( CalculationRepetitionNumber ) - 1

] ;

Case (

mode = "Dates" ;

d - DayOfWeek ( d ) + 1 + i ;

mode = "Weeks" ;

d - DayOfWeek ( d ) + 1 + 7 * i ;

mode = "Months" ;

Date ( 1 + i ; 1 ; Year ( d ) ) ;

mode = "Quarters" ;

Date ( 1 + 3 * i ; 1 ; Year ( d ) ) ;

mode = "Years" ;

Date ( 1 ; 1 ; Year ( d ) + i )

)

)

And a bunch of column keyfields, with cColumnKeyX (result is Text) =

Left ( gMode ; 1 ) & " " & GetRepetition ( cAnchorDatesR ; X )

[LaRetta]

Houston?! We don't have ignition! Owner pulled me off for another project. But I've made quite a bit of progress on this project and hope to finish it yet tonight/morning sometime!

I corrected my calc (and the version displayed above). Please note that I added Fiscal year (and to save confusion, refer to calendar year as C since I needed TWO 'year' dates. RE: Fiscal Quarter vs. Calendar Quarter ... For the life of me, I kept thinking I needed a different set of Quarterly depending upon whether it was fiscal or calendar quarter. But ... I can't prove I'm right (or wrong) on that. Because listing out quarterly months is:

CALENDAR:

1 = Jan, Feb, Mar

2 = Apr, May, Jun

3 = Jul, Aug, Sep

4 = Oct, Nov, Dec

The calendar year is the year the date falls in ... period!!

FISCAL

1 = Oct, Nov, Dec of the prior year [color:red]error in thinking is here, I believe

2 = Jan, Feb, Mar

3 = Apr, May, Jun

4 = Jul, Aug, Sep

YEAR fiscal spans years but QUARTER fiscals never will (they will always be the year they are in also). It's amazing how some things can trip us, isn't it? So I'm still unclear on whether I need another 'identical' Quarter calculaton listed in addition to the Q. At this point I'm leaning towards "No, not necessary." Any unseen caveat I haven't considered?

LaRetta

[comment]

The basic priciple is to associate each date with the starting date of a specific period. Since your fiscal quarters have the same starting periods as your calendar quarters, one calculation covers both. You will want to play with column labeling on the left side, since the NUMBERING SCHEME is different for fiscal quarters.

Your calc for fiscal year doesn't look right: the subtraction is a function of the MONTH, not the year. What you have is affecting EVERY MONTH in every ODD year. Try:

"F " & Date ( 10 ; 1 ; Y - ( M < 10 ) )

although

"F " & Date ( 10 ; 1 ; Y + ( M > 9 ) )

would seem more reasonable to me (i.e. the year is determined by the majority of the months). As long as this is behind the scenes, it doesn't matter, but again I'm thinking of labeling on the left side.

[LaRetta]

"What you have is affecting EVERY MONTH in every ODD year."

Of course. I see it now. I started to use Month for boolean test but wanted to try Mod() with Year. It worked perfectly on the TWO consecutive years (same month/day) I tested.

Thanks for the catch, Michael! ;)

[LaRetta]

WOW. This has taken some digesting. I have the prettiest dynamic portals you've ever seen! Thank you for suggesting I leave it as dates until tested. As numbers, I would have no idea what I was viewing!

cDateMultiKey: I needed to use your first calc - "F " & Date ( 10 ; 1 ; Y - ( M < 10 ) ). The second calc, when viewing an Invoice dated 8/2/2005, produces 10/1/2005. But that Invoice's FIRST fiscal-year date (always looking backwards and then projecting forwards) should be 10/1/2004. [color:red]I corrected cDateMultiKey calc above also. And I assumed that, by assuring base always contained a date, the calc would always evaluate. But since some orders aren’t yet shipped (have ScheduledShipDate but not PhysicalShipDate), I had to uncheck ‘Do Not Evaluate if All (any) Referenced Fields are Empty.’ I almost missed that again.

cAnchorDateR: I placed all of these fields (including repetitions and ColumnX keys) on list layout, attached pop-up to gMode and my heart began to pound as I watched the magic!! Using 'i' to extend the dates (or group of dates) dyamically in INCREDIBLE!!! Incrementing according to the rep number (i)! Just add repetitions if you want to extend - never touch the calc again! WOW! This whole process was a wonderful date-teaching (and Repetition teaching) tool!

I was surprised that on Weeks you used + 1 + 7 instead of just +8 but the results are off if you combine them. Maybe because division and multiplication are applied first and it needs to apply to the 7? Well, I applied that theory elsewhere also and it seemed to hold. And I changed some of the other calcs (Lord help me). Month started at month 1 but (I think) should start the Month-beginning as specified in gDate, right? And then look forward from there? Same with Quarter and year. If I went the wrong direction and messed up anything, please let me know! Because now I can whip these puppies out any which way you want, thanks to you!! :

So your calculation cAnchorDatesR is now:

Let ( [

d = Extend ( gDate ) ;

mode = Extend ( gMode ) ;

m = Month(d) ;

y = Year(d) ;

i = Get ( CalculationRepetitionNumber ) - 1

] ;

Case (

mode = "Dates" ;

d + i ;

mode = "Weeks" ;

d - DayOfWeek ( d ) + 1 + 7 * i ;

mode = "Months" ;

Date ( m + i ; 1 ; y ) ;

mode = "Quarters" ;

Date (m - Mod( m - 1 ; 3) + 3 * i ; 1 ; y ) ;

mode = "Fiscal" ;

Date ( 10 ; 1 ; y - ( m < 10) + i ) ;

mode = "Calendar" ;

Date ( 1 ; 1 ; y + i )

)

)

Notice that I also added M & Y variables for my convenience (hope that was okay). I think I'm ready to move to labeling. I believe this is one of the most powerful techniques I have EVER learned. Michael, this really rocks!!! I adore date calcs now! And repetitions! I can't thank you enough for teaching me this!!

LaRetta

[comment]

on Weeks you used + 1 + 7 instead of just +8

No, it's + 1 + 7*i, as you have in your final calc. Multiplication comes before addition.

d + i

In my version, when gMode = "Dates", the first column is always Sunday. Same as when gMode = "Months", the first column is January.

Yes, it is a powerful method, if I may say so myself. But that's not all. If you format cAnchorDatesR differently on each layout, it can (almost) do all the labeling by itself. So with 7 columns, it can say |Sunday|Monday|.., with 12 columns it can be |Jan 05|Feb 05|..., with 4 or 8 columns it can be |2005/Q1|2005/Q2|..., and so on.

Sadly, dates cannot be formatted to show weeek numbers, but if you can get away with adding a text label "Week beginning ..." or something like that, above the date, then that's all the labeling you'll ever need.

That's why I suggested shifting fiscal year ahead - it seems to me that a fiscal year beginning on October 2004 should be labeled 2005.

[LaRetta]

on Weeks you used + 1 + 7 instead of just +8

No, it's + 1 + 7*i, as you have in your final calc. Multiplication comes before addition.

Yes! That's what I said, I just displayed part of the forumula for my example and not all (ooops). I just meant that I had to figure that out, you see? I would tend to want to write 1 + (7 * i). To be able to watch the example you had given me prior (that multiplication comes before addition) in ACTION ... to understand ... it's very exciting for someone whose last math was Algebra 38 years ago!! And I didn't want to use ANY of it without totally understanding ALL of it. That ... to me ... is the difference between great respect and blind worship. It would have been easier to blindly trust you on all of this and just go to bed. But I had a great opportunity I refused to pass up and it has changed my perspective.

In my version, when gMode = "Dates", the first column is always Sunday. Same as when gMode = "Months", the first column is January.

Dates are days. Weeks look back to Sunday. I envisioned User putting any date in gDate, then pop-up on Dates. From that day forward would expand with days. Change popup to Weeks, that (prior) SUNDAY forward would expand; Months, beginning of THAT month and forward would expand. If they wanted to display starting January (and forward), they would put a date from January or select Calendar Mode! But now I can run this puppy any way we wish because I GET IT. And that tickles me beyond belief!!

Oh! Labeling! This will also be incredible to play with and learn also. I was wondering what you envisioned for the labeling. So much to play with here.

That's why I suggested shifting fiscal year ahead - it seems to me that a fiscal year beginning on October 2004 should be labeled 2005.

Labeled 2005 on fiscal displays, yes. But the data accumulated in that relationship should be October 2004 through September 2005. If I didn't shift backwards, my Fiscal Year totals would not sum the correct data. Adjust backwards relative to the gDate, expand forwards for as many columns as I wish to display! :

Well, I only got 3 hours sleep and I've NEVER felt better in my LIFE!! I will OWN this entire comcept (inside) and will be using it a LOT!! I leanred more about manipulating date calcs from this thread than my prior 22 months using FM because I could see it in action as I manipulated it. It's like watching mathematics PERFORMED before my eyes - that's the power of it to me. Changing the formula doesn't just change one number to some mysterious d'ifferent' number ... it shows the logic in action as it expands! THIS I can understand! It's truly a wonder. I can NOT thank you enough, Michael! :

LaRetta

[comment]

You are mixing two issues:

First, there is GROUPING of records - this is done by computing something that will be both common and unique to the group. I picked the starting date of the period (with a identifying letter), but it will work with anything that satisfies these two conditions. So for fiscal year, you can safely group the records by "F " & the beginning of the next fiscal year. Or the year after that. Or 100 years from now.

DISPLAYING the grouped data in columns is another issue. I would prefer to keep my date columns organized in a by-week type arrangement, and my months and quarters in a by-year view. Clicking on arrows on each side would get me to next/previous week in one view, or to next/previous year in another. I (as the user) don't need to see gDate at all - except in a custom dialog box, after clicking "Go to date...". Just like a calendar. But it is only a matter of preference.

[LaRetta]

It's only a matter of preference - the User preference! And I can now give them anything they wish. And you just gave me MORE ideas! I don't want to go to work! I want to give them all the displayed portal data they could dream of! And I don't have to create 36 (or 100) portals to do it!

One final question (aren't you glad, blush) ... just a speculation mind you ...

I could even use a global calculation to modify (toggle) between different 'styles.' As you've indicated, the User preference may vary. There is really no limit to how the left-side can be handled, correct? In other words, this is just ONE join line - I could still include regular portal filters on other join fields using = (such as filtering by Contact Rep, Product Type, etc)?

Oh! I'm happier than a pig in mud.

UPDATE: Oh! And I can mix portals as you say! 4 week displays then month and yearly next to it!! I am unlimited!! Same portals, same relationships, same calcs - different displays!!!

L

Edited August 9, 2005 by Guest

[comment]

Yes, of course. In fact, in the demo I have made for myself for this, you can flip between Parent records, and the displayed child records change. A global (or several) could filter this on demand.

[LaRetta]

It just hit me and I had to look. I set my cColumnX keys as unstored calcs. But they should be globals, right? But they don't HAVE to be, ie, if I'm on parent record of the Contact Rep, THAT also could provide the filter. Hmmm, getting myself a bit 'out there' now, I think. But I had made everything unstored calcs but I could use global calcs instead. It's currently in my Reports table where each record is a pre-defined Report. Users can select a report (record) and that currently is what creates the match to my old portals. I did this so that I wouldn't be running globals across the network.

This blows my mind.

L

[comment]

I believe the calcs CAN be globals - if you promise not to use Get (CurrentDate) in the chain.

I was always curious if a global calc takes less resources than a regular calc that references only global fields.

[LaRetta]

I've been chewing on global calcs for a while now. Because, since they can be seen from anywhere same as regular globals (?) even without need of relationships (except of course when used specifically for relationships), they are certainly more powerful (?) in many respects.

And I promise not to use Get(CurrentDate). I'm off to work!! I'm late for a very critcal sales meeting and I don't even care! :