topic Re: Fiscal month sort order in QlikView

Fiscal month sort order

Fri, 30 Jan 2015 11:14:43 GMT

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

Re: Fiscal month sort order

Colin-Albert — Fri, 30 Jan 2015 11:20:41 GMT

Create your FiscalMonth as a dual field, and sort as a numeric

dual(Text value, numeric value) as fieldname

Re: Fiscal month sort order

sunny_talwar — Fri, 30 Jan 2015 11:25:41 GMT

Try sorting month by the load order.

Best,

Re: Fiscal month sort order

avinashelite — Fri, 30 Jan 2015 11:36:27 GMT

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

Re: Fiscal month sort order

Fri, 30 Jan 2015 12:14:22 GMT

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

Re: Fiscal month sort order

Fri, 30 Jan 2015 12:16:17 GMT

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

Re: Fiscal month sort order

Colin-Albert — Fri, 30 Jan 2015 12:19:27 GMT

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.

Re: Fiscal month sort order

jonathandienst — Fri, 30 Jan 2015 12:21:28 GMT

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

Re: Fiscal month sort order

Anonymous — Fri, 30 Jan 2015 12:32:37 GMT

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

Re: Fiscal month sort order

Fri, 30 Jan 2015 12:36:12 GMT

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.

Re: Fiscal month sort order

MarcoWedel — Fri, 30 Jan 2015 12:47:53 GMT

Dual(MonthName(AddMonths($(vDateField), -3)) & 'FY',MonthName(AddMonths($(vDateField), -3))) as FISCAL_MONTH_YEAR

Re: Fiscal month sort order

Fri, 30 Jan 2015 16:47:21 GMT

Ah still no joy Im afraid ....

Re: Fiscal month sort order

sunny_talwar — Fri, 30 Jan 2015 16:52:53 GMT

Will you be able to share a sample data or sample qvw file for us to see what is not working?

Best,

Re: Fiscal month sort order

Fri, 30 Jan 2015 17:31:41 GMT

Marco - thanks this worked perfectly and in line with what Colin mentioned previously.

Thanks all