Last Month

[Harry]

Hello Board,

 

I'm stuck, again. And again i call for your help. I am building a dashboard, i have an 'Hours' table, a 'Day' table, a 'Weeks' table and now i'm trying to build a 'Months' table; all aggregating data so i can chart and summarise it by these divisions.

 

I'm totally stuck on this one and I can't make it work. I'm sure i'm missing something but i just don't know...

 

What I need is a MonthYear number to Relate records with that date. So, 5/2014 for this month. I can generate this from my 'Today' field which is auto inputted into the Dashboard table. But how do i get last month, 4/2014? And 3/2014, 2/2014, 1/2014, 12/2013?

 

I have 12 records in the Months table, with an Index Month field, auto incremented from 0 to 11. So, i was hoping to use this number as a 'CurrentMonth - MonthIndex' type thing.

 

I have tried Month(CurrentDate)-1 but that doesn't quite work as I thought it would. I can go back to January 2014 but further i get December 2014, instead of December 2013.

 

I don't need to take a month off, so i didn't think i would need to get complicated with defining month start/end dates.

 

Thank you for any help you can give. You're all aces.

 

Harry

[Harry]

Actually, it gives minus number for last year, rather than December etc.

[eos]

I have tried Month(CurrentDate)-1 but that doesn't quite work as I thought it would. I can go back to January 2014 but further i get December 2014, instead of December 2013.

 

Rather than performing math on just the Month component, use the complete date, then pick it apart again; e.g. …

Let ( [
  cd = Get ( CurrentDate ) ;
  fiveAgo = Date ( Month ( cd ) - 5 ; 1 ; Year ( cd ) )
  ] ;
  Month ( fiveAgo ) & "|" & Year ( fiveAgo )
)

returns (today) "12|2013"

[Harry]

Thanks eos.

 

I've largely (read: completely) skirted around the LET function until now.

 

Can you explain the calculation a little? The '- 5; 1;' is part of which function?

I will read up on it.

 

Thanks!

[eos]

Harry, on 20 May 2014 - 1:22 PM, said: Can you explain the calculation a little? The '- 5; 1;' is part of which function?

Put today's date into a variable; let FileMaker calculate the first day of the month five months previous (by subtracting from the Month), and put the result into another variable; finally, pick the Month() and Year () portions from that variable and concatenate them into a text result.

Harry, on 20 May 2014 - 1:22 PM, said: I've largely (read: completely) skirted around the LET function until now.

There's nothing mysterious about Let(); the first part lets you declare variables, and the second part is just a plain old calculation – except that within it, you can reference the variables you declared before. This makes (can make …) for a much more readable (i.e. maintainable) calculation. It can also have performance implications, since FileMaker would have to read a (potentially large) field value only once, then stash it away in memory under a variable name that you can reference. Consider: Let ( [ a = myVeryLongFieldNameWhichForReasonsUnnamedCannotBeChanged ; b = anAlmostEquallyLongFieldNameWhichDoesntQuiteMakeIt ; c = shorterFieldNameButStillAnnoyinglyLong ] ; Case ( a > 0 ; b * 3 ; a < 0 ; c * 3 ; b * c //whatever all this may mean … ) ) Imagine the variables a, b and c in the calculation being replaced with the field names they refer to … (and it gets worse if you do this in a script). Point of fact, just take the formula suggested above; here's what would look without Let() (and without any formatting): Month ( Date ( Month ( Get ( CurrentDate ) ) - 5 ; 1 ; Year ( Get ( CurrentDate ) ) ) ) & "|" & Year ( Date ( Month ( Get ( CurrentDate ) ) - 5 ; 1 ; Year ( Get ( CurrentDate ) ) ) ) Since you have FM Advanced, why not use the Data Viewer to play around with Let() and some test data? You can e.g. paste the calculation from my first post above “as is” and try to tweak it a little.

[Rick Whitelaw]

Whatever calc you use it should be un stored. This may be the source of your problem.

[LaRetta]

 I am building a dashboard, i have an 'Hours' table, a 'Day' table, a 'Weeks' table and now i'm trying to build a 'Months' table; all aggregating data so i can chart and summarise it by these divisions.

 

 

I'm afraid that creating separate tables for parts of a date is inefficient and costly.  You should use the records where the date exists ... no need for additional, separate tables at all, Harry.  :-)

[Harry]

Hi LaRetta,

Thanks for your input.

I'm not really sure what you mean. I saw lots of videos and read HowTo's about building dashboards and all of them built separate tables to do the aggregated mathematics; pulling in and summarising data from other tables. They mostly fixed on having a 'Months' table that defined months using calcs and then related to the data tables, filtering by those relationship.

I just can't think how I'd do this without having a separate table. How do i get a graph with months or days or weeks or minutes across the bottom and my data counts etc on the Y? It took me long enough to get my head around doing it like this!

[Harry]

Just re read your post again and if I can add to my explanation: I have lots of tables with data, some is timestamped, some is dated. I use the different tables to aggregate different tables based on different criteria. Some data i want to know by the hour, some by the day, some per week and then management figures and data by the month/rolling quarter/year etc etc.

 

Not sure if that helps explain my setup or not.... 

 

I'm not building extra tables to mirror one table with data recorded in different parts.

[Harry]

Let ( [
  cd = Get ( CurrentDate ) ;
  fiveAgo = Date ( Month ( cd ) - 5 ; 1 ; Year ( cd ) )
  ] ;
  Month ( fiveAgo ) & "|" & Year ( fiveAgo )
)

returns (today) "12|2013"

 

OK, read it and this little scripting works perfectly, thank you again! Finally understand.