Crosstab Sort Where 'Month Name' Is Nested Under 'Fiscal Year'

[Cognos_Jan2017]

We have a Query Calculation, which works perfectly, when Fiscal Year 2017 is the only FY selected w/ code, as Sort for 'Month Name' ...

Case
When 'Month Name'='October' Then 1
When 'Month Name'='November' Then 2
When 'Month Name'='December' Then 3
When 'Month Name'='January' Then 4
When 'Month Name'='February' Then 5
When 'Month Name'='March' Then 6
When 'Month Name'='April' Then 7
When 'Month Name'='May' Then 8
When 'Month Name'='June' Then 9
When 'Month Name'='July' Then 10
When 'Month Name'='August' Then 11
When 'Month Name'='September' Then 12
End

It makes sense when selecting FY 2016 and 2017, the above sort code fails, but only for FY 2016.
=======================
Tried writing another Query Calculation of (Below) ... sorted properly for FY 2017 but not for FY 2016 ... It sorts like the 1st Query Calculation above when selecting FY 2016 and FY 2017.
Case
When 'Month Name'='October' And 'Fiscal Year'=2016 Then 1
When 'Month Name'='November' And 'Fiscal Year'=2016 Then 2
When 'Month Name'='December' And 'Fiscal Year'=2016 Then 3
When 'Month Name'='January' And 'Fiscal Year'=2016 Then 4
When 'Month Name'='February' And 'Fiscal Year'=2016 Then 5
When 'Month Name'='March' And 'Fiscal Year'=2016 Then 6
When 'Month Name'='April' And 'Fiscal Year'=2016 Then 7
When 'Month Name'='May' And 'Fiscal Year'=2016 Then 8
When 'Month Name'='June' And 'Fiscal Year'=2016 Then 9
When 'Month Name'='July' And 'Fiscal Year'=2016 Then 10
When 'Month Name'='August' And 'Fiscal Year'=2016 Then 11
When 'Month Name'='September' And 'Fiscal Year'=2016 Then 12
When 'Month Name'='October' And 'Fiscal Year'=2017 Then 13
When 'Month Name'='November' And 'Fiscal Year'=2017 Then 14
When 'Month Name'='December' And 'Fiscal Year'=2017 Then 15
When 'Month Name'='January' And 'Fiscal Year'=2017 Then 16
When 'Month Name'='February' And 'Fiscal Year'=2017 Then 17
When 'Month Name'='March' And 'Fiscal Year'=2017 Then 18
When 'Month Name'='April' And 'Fiscal Year'=2017 Then 19
When 'Month Name'='May' And 'Fiscal Year'=2017 Then 20
When 'Month Name'='June' And 'Fiscal Year'=2017 Then 21
When 'Month Name'='July' And 'Fiscal Year'=2017 Then 22
When 'Month Name'='August' And 'Fiscal Year'=2017 Then 23
When 'Month Name'='September' And 'Fiscal Year'=2017 Then 24
End

Ideas?  TIA, Bob

[Cognos_Jan2017]

My fault ...

Code wasn't fully qualified.  Works now.  ONLY "issue" is, apparently,
the need to hardcore for every possible selected Fiscal Year.

Anyone w/ an idea how to avoid that?

TIA, Bob

[BigChris]

Have you got anything that tells you what the current fiscal year is? If you do then you might be able to come up with something along the lines of:

Code Select Expand

Case
when [Fiscal Year] = [Current Fiscal Year] then 12
else 0
END +
Case
when [Month Name] = 'October' then 1
when [Month Name] = 'November' then 2
etc.
END

A more elegant solution would be to construct and populate a calendar table in your database, but I don't know if that's an option for you.

[zakatak]

My preference is to create a mathematical formula like the following:

[Fiscal Year] * 100 + case when Month(Date) <= 9 then Month(Date) + 12 else Month(Date) end - 9

The resulting values (201701, 201702, 201703, etc.) are all numeric values that can be sorted.

[bdbits]

It appears to me that you are trying to create a sort key based on a fiscal calendar.

In which case I have to ask why the fiscal month is not in your time dimension. You might want it to include the year - or have another item like [Fiscal Sort Key] - for sorting purposes.

Either way, I think this belongs in your time dimension.

[Cognos_Jan2017]

BigChris, zakatak, and bdbits ... thank you.

The Report is in a new Package we rec'd this week from the 3rd Party software company.

I did find a Query Item 'Fiscal Year #' which sorts correctly for the Fiscal Year which begins in October.

I added 'Fiscal Year #' to the Report's Query, and set the Sort for the 'Month Name' Query Item to Ascending 'Fiscal Year #'.

All WORKS !!!  Thank you all.