Need to find max date then calculate, then next max date etc

[Stu412]

Hi there

I'm a bit stuck here and need some help around dates.  I have a data set containing values which  need to display in columns across the page, left to right in order of date with the most recent on the left.  The data is on my table and each record has a date and I'll be summarising depending on other criteria  Under normal circumstances, I'll need to display a column per year: 2014, 2013, 2012, 2011 etc.  To do this and allow me to easily extract the figures to the correct column:

I calculate the most recent year - RecentYear: Summary Maximum on the date field

I split out the year alone - Year: Right(Date,4)

I calculate the highest year - MaxYear: Right(RecentYear,4)

I calculate my required values on this - ThisYear: Case (MaxYear = Year ; Amount)

I calculate my further required values - LastYear: Case (MaxYear - 1 = Year ; Amount) and so on for the older years.  

Then I can just place the values on the page.  

It works well until I have two sets of data for the same year...

These still need to be split out, with the most recent by date to the left and the next one alongside, even though it's in the same year.  

I'm struggling to adapt the MaxYear-1 part of the calcs above, which I use to set the next lowest column to work with an entire date.  Just using a '<' operator wouldn't work as there are other years which are all going to be lower than the maximum date.

It needs to be dynamic so the most recent date's figures always appear in the 'ThisYear' calculation, and then the next most recent date's figures always appear in 'LastYear'.

Really need some help on this, thanks in advance

 

 

 

[comment]

You lost me in the first paragraph: what method will you be using to display the data in columns? Filemaker does not provide dynamic columns natively.

Once you explain that, we might be able to figure out what exactly is it that you are trying to calculate.

 

[Stu412]

Hi Comment, I feared I may do that, apologies.

I've had some caffeine and a ponder, and given that FM doesn't do columns, I usually create my own criteria to make my own columns, which I then summarise and calculate on.  I use case statements to act on this criteria to ultimately get the right figures in the right column/field.

If I can get to the following position, I should be ok:

Date              SortField    Amount

31/01/2015    1               10000

31/01/2015    1                10000

31/01/2015    1                15000

30/11/2014     2                5000

30/11/2014     2                4000

31/10/2014     3                10000

31/10/2014     3                10000

31/10/2014     3                  5000

I don't know how to work out the SortField which is based on the dates.  If I can get that, I'll set up these further fields to give me my columns:

• Current = Case (SortField = "1" ; Amount)
• sCurrent = Summary(Current) which is 35000
• Previous = Case (SortField = "2" ; Amount)
• sPrevious = Summary(Previous) which is 9000
• Last = Case (SortField = "3" ; Amount)
• sLast = Summary(Last) which is 25000

I then display the summary fields:

sCurrent     sPrevious     sLast 

on my print layout so I get the summaries side by side.

Hopefully that's a little clearer.  It's the sort field that's stopping me now, I just cannot see how to generate those numbers.

Thanks 

[comment]

Aren't the records in column 3 supposed to be in year 2013?

[Stu412]

Not necessarily - it's possible to have two sets of data for the same year, but different months, hence the example above.  

[comment]

it's possible to have two sets of data for the same year, but different months

I am afraid I don't follow. What determines which data goes to which set?

Edited July 31, 2015 by comment

[Stu412]

That's the question I had this morning, which in turn led me to think about creating a SortField.  The data example above is a mock up of what I need, with anything in SortField 1 being the first column, 2 is the middle column, 3 is the last column and so on.

The SortField needs to be a calculation of some sort to determine whether a 1,2 or 3 is arrived at, based on the date of the records.  

For the most recent records with a like date, it'd be SortField=1.  The next most recent records with the same date would be SortField=2 and so on down the record list.

[comment]

I am sorry but I am still unable to get past the first stage, which is understanding what needs to be done here, before getting to the question of how.

I gather you want divide your data into three fixed columns. Is that correct? If yes, please explain how would I assign each record to a column if I were doing this manually.

[Stu412]

Each column is date specific, so from the example above, we'd have column headers of:

31/01/2015        30/11/2014        31/10/2014

10000

10000

15000

                          5000

                          4000 

                                                    10000

                                                    10000

                                                    5000 

-----------------------------------------------------

35000                9000                  25000

[comment]

Could you not take the easy way out and present your data in a traditional report , e.g.

31/01/2015
    10000
    10000
    15000
35000

30/11/2014
    5000
    4000
9000

31/10/2014
    10000
    10000
    5000
25000

The thing is, there's no way a record can calculate its column number without knowing how many distinct dates are there in the current found set that are later than this record's date. And there's no way to know this by calculations alone: you would have to script this.

[comment]

there's no way a record can calculate its column number

Well, maybe there is. If you define a value list "AllDates" to use values from the Date field, and a summary field sListOfDates as List of: Date, you could then do =

Let ( [
allDates = ValueListItems ( Get ( fileName ) ; "AllDates" ) ;
foundDates = FilterValues ( allDates ; sListOfDates ) ;
n = ValueCount ( foundDates ) ;
index = ValueCount ( Left ( foundDates ; Position ( ¶ & foundDates & ¶ ; ¶ & Date & ¶ ; 1 ; 1 ) ) )
] ;
n - index + 1
)

Caveat: untested.

[Stu412]

Comment, thanks for that, I'll give this a try first.  I was planning a script to sort the found set by date and compare date fields to a variable holding the prior record's date.  If it's the same, use the same SortField number 1, else +1. Caveat: Completely untested.

Potentially two solutions.

 

Amazing! That is way too cunning

I now have a lovely SortField which I can use in case statements for the various yearly columns required.

That is brilliant, thanks so much!!

[comment]

I now have a lovely SortField which I can use in case statements for the various yearly columns required.

You could make it a repeating calculation field with 3 repetitions =

Let ( [
thisDate = Extend ( Date ) ;
allDates = ValueListItems ( Get ( fileName ) ; "AllDates" ) ;
foundDates = FilterValues ( allDates ; sListOfDates ) ;
n = ValueCount ( foundDates ) ;
index = ValueCount ( Left ( foundDates ; Position ( ¶ & foundDates & ¶ ; ¶ & thisDate & ¶ ; 1 ; 1 ) ) )
] ;
Case ( n - index + 1 = Get ( CalculationRepetitionNumber ) ; Extend ( Amount ) )
)

and define a summary field as total of this field, with the option to summarize repetitions set to "Individually".

If scripting, the script could be much simpler if you employ a method known as Fast Summaries.

I am still puzzled why you call them yearly columns. They show the last three dates of your found set, with no regard for year.

 

 

Edited July 31, 2015 by comment

[Stu412]

Initially the data sets started out as defined years, so I designed around this structure.  Recently, there have been some data sets crop up where data exists for two points in a year which need to be distinct in columns, but under the existing method would summarise together.  It's more of an exception than the rule, but it needs to be covered as an eventuality.

 

[LaRetta]

I calculate the most recent year - RecentYear: Summary Maximum on the date field

I split out the year alone - Year: Right(Date,4)

I calculate the highest year - MaxYear: Right(RecentYear,4)

I calculate my required values on this - ThisYear: Case (MaxYear = Year ; Amount)

I calculate my further required values - LastYear: Case (MaxYear - 1 = Year ; Amount) and so on for the older years.  

Then I can just place the values on the page.  

 Then you just place the values on the page where?  In the header of the list of records?  On a form layout?  What about the detail of each record?

I am intrigued by how you are displaying this.  From your example of the columns, the first 'page of the report' might only have values in the first column if there are too many individual records to display on first page, then the second page might only display values from second column and so on.  How many records in your found set are we talking about normally?  Will it always be a maximum of the current 'date' and the prior two unique dates?  Columns can only go so wide if printing or saving as PDF.

Can you provide a screen shot of the report?  Are you displaying in browse mode on layout, printing, saving as PDF?  What fields show from each record in the column?  

I still think there might be easier ways of handling this if we knew more. :-)