Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have a bar graph with a cyclic group to toggle between calendar months and fiscal months.
My code for the fiscal month is :
,MonthName(AddMonths($(vDateField), -3)) as FISCAL_MONTH_YEAR
In order to distinguish this as fiscal I wanted to add the 'FY' suffix as such:
,MonthName(AddMonths($(vDateField), -3)) & 'FY' as FISCAL_MONTH_YEAR
but when I do so it then totally messes up the sort order. Months are all over the place ie. The current sort order for the cyclic group is
set to numeric - ascending for both calendar and fiscal.
Any help /ideas much appreciated.
Thanks
Dual(MonthName(AddMonths($(vDateField), -3)) & 'FY',MonthName(AddMonths($(vDateField), -3))) as FISCAL_MONTH_YEAR
Create your FiscalMonth as a dual field, and sort as a numeric
dual(Text value, numeric value) as fieldname
Try sorting month by the load order.
Best,
S
Hi,
Try like this:
Go to Sort Tab and select Expression> then use this express MonthName(AddMonths($(vDateField), -3)) and sort according to your need
Hi I tried this but only the FY part is displayed as opposed to say FY Jun 2013
Dual('FY',(MonthName(AddMonths($(vDateField), -3)))) as FISCAL_MONTH_YEAR
Thanks I tried the various sort orders mentioned on the thread but months are still messing up. For instance FY Q1 2013 will show May 13, Apr 13, Jun 13 as opposed to April 13 ,May 13, Jun 13
For the dual value, the text field needs to show the full text that you want to display e.g. "FY Jun 2013" as an expression, and the number would be the numeric sort order for the text. e.g. 201302 if Jun 2013 is the second period of the fiscal year.
Hi
Are you aware that you can set the sort orders for the individual fields of the cyclic group? Add the sort order for the field on the cyclic group editor.
This of source assumes that your months are properly loaded in order (to use Load Order) or are duals (to use numeric order) with numeric values in the correct order.
HTH
Jonathan
Hi,
Try as Below:
Go to Sort Tab and Enable Expression> give this expression:
Pick(Month,'Jan','Feb','March','April','May','June','July','Aug','Sep','Oct','Nov','Dec') and Sort Asc/Desc to your requirement
Regards
Neetha
Thanks Jonathon - Yep I know you can amend the sort order of the actual group. Thing is it only messes up when I add the FY suffix.