FileMaker Pro - Graph Sort Issue
I have created a Bar Chart - which summarises a simple count of Weekly Deliveries (Fiscal Week) for a Project which began in August 2013. The Graph is based on a Summary Count Table entitled "Delivery Week" using two components which are Unstored - brought together from a Date Field entitled "Brought Into ServiceDate" - the two components being 'Week of Fiscal Year" along with the "Year" from the Brought Into service Date.
So that each column gives a Count entitled 'Week 1 2013, Week 2 2013 - etc.
The Actual Calculation is: "Week " & WeekOfYearFiscal (Brought Into Service Date;7) & " " & Year (Brought Into Service Date)
This Table is then used to create the Bar Chart.
But - although the Data in the Table is sorted - and displays Week 1, Week 2 etc - the Year is ignored in the Sort - so that Week 29 2014 (the week we are currently in at time of writing) is shown in the Graph - BEFORE - Week 35 2013 (which is the first week in which the new services were delivered (in my project)).
As we approach Week 35 2014 - this will become a problem - as the Graph will show Week 35 2013 - alongside Week 35 2014 (note It has always been a problem as Deliveries in 2014 - have always been shown before Weeks in 2013) but will really become a Mess when we start to duplicate the Fiscal Weeks)
What am I missing? - for the Graph to recognise that Weeks in 2013 should be displayed before the Weeks 2014.
I have attached a snapshot of the Graph - the problem can be seen at Week 53 2013 (which in Fiscal Week Terms was a split week - Part in 2013 - part in 2014 - at the end of the Graph).
292014 is less than 352013 is it not? FileMaker is sorting the way that you told it to, not the way that you want it to.
Sort on a different calculation field that puts the year before the day:
Year (Brought Into Service Date) & Right ( "0" ; WeekOfYearFiscal (Brought Into Service Date;7) ; 2 )