date order in chart

[#1]

Hi,

We have a chart which monitors satisfaction by month/qaurter. The chart shows satisfaction for the quarter (bar) and each month (line).

Chart works absolutely fine bar the order of dates at the bottom.

The chart should read

May/Q1      Jun/Q1          Jul/Q2          Aug/Q2          Sep/Q2          etc

however, it doesn't like May and so won't go in correct order despite options I pick - see screenshot attached.

The solution will be in the expresion but can anybody help?

FYI this is the expression used in the script to craete month/qaurter for the chart,

Month(Date([Date Survey Completed], 'DD/MM/YYYY')) & '/' & 'Q' & (Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter,

Chris

[miguelbraga]

I think that the problema of your bar chart is that you don't have correctly add the quarter to the month, because Q1 is from January to March, Q2 is from April to June, Q3 is from July to September and Q4 is from October to December, not like you have.

It should be like this:

'Q' & ceil(month( [Date Survey Completed] )/3) as Quarter

Not like this:

'Q' & ceil(month( [Date Survey Completed] - 3)/3) as Quarter

By making the quarter right, it should be ordered correctly.

Miguel

[#3]

Hi Miguel

Sorry we work in financial years so:

Q1 = April, May, Jun

Q2 = July, Aug, Sep

Q3 = Oct, Nov, Dec

Q4 = Jan, Feb, Mar

Chris

[maxgro]

I think you can adapt the fiscal calendar here (or get an idea)

Fiscal and Standard Calendar generation

I just  try using the calendar in the link above, changing the fiscal month

Dual(Text(Date(MonthEnd(Date), 'MMM')) & '/Q' & Ceil(FiscalMonth/3), FiscalMonth) AS FiscalMonthName;

and it seems ordered

[#5]

Hi Maxgro,

Great but not 100% how to do this correctly. I have attached the app now. Do we need to set a start and end date?

However, regardless of fiscal calender trying to understand why this issue is happening. You will see the chart at the minute is displaying mistake first mentioned (months not in order); however, if we only bring one service area through (e.g ASB) then chart works fine. Also same existing script for chart is used on individual apps for each service and this also works.

Really weird!

Chris