Display Months in a Different Order

[#1]

Our fiscal year is from Feb-Jan. I think I have gotten the time periods and hierarchy setup to display my data in the correct fiscal years, quarters, and months but when displaying charts using these as dimentions, the months display in a calendar year format. Here is a screenshot of a chart and a list box I display.

In the chart above, the 4th Quarter is Nov-Jan but the chart displays as Jan, Nov, Dec. The data is correct but the chart displays it in calendar year order. Same with the list box of Months. I would like it to display Feb-Jan.

Any ideas?

[#2]

Not sure how you have set up the calendar. But you could try sorting all your charts and listboxes with Months by 'Load Order'. eg. we have fiscal year starting from April. I have a simple calendar like

LOAD * INLINE [
Month, MonthName, Quarter
1, Apr, Q1
2, May, Q1
3, Jun, Q1
4, Jul, Q2
5, Aug, Q2
6, Sep, Q2
7, Oct, Q3
8, Nov, Q3
9, Dec, Q3
10, Jan, Q4
11, Feb, Q4
12, Mar, Q4
];

For months, I sort on Load Order and everything works well.

Nimish

[#3]

Thanks Nimish. That looked promising. When I tried it, I ended up with 24 months in my list box. When loading data from another table, I use

Month (date) as Month (first Month is the function that pulls the month string out of date)

Seems QV is making a distinction between the value loaded from the INLINE table and the value returned by the Month function. For example, when I use the value 'Feb' as loaded from the INLINE table as a current selection, I get no data. But when I use the value 'Feb' returned by the Month function as the current selection, I get the data I expect.

Any suggestions?

[#4 · Accepted solution]

If you are going to use the inline script similar to mine then you may want to try using

text(Month(date)) as MonthName in your script. This will then get associated with the inline calendar

Nimish

[#5]

or you could use

num(month(Date)) as Month in your script

Nimis

[#6]

Bingo! text(Month(date)) worked great!

Thanks!

[Davidoff]

hi all,

I appear to be having the same problem, even though i tried an inline calendar before looking here for the answer! And i've given in and created the Master calendar table; still no joy!!

My companies fiscal year begins in august. I have a pivot table showing data for each month, but on each occasion i can get 2010 data starting in August, but after December it goes back to January 2010 etc. rather than nothing at all, or Jan 2011 onwards. Or i can get the months listed from August, but the data underneath then shows under the wrong month! (Januarys data shows under august)

Tables:

MasterTable with KPIDate, ordered by KPIDate

MasterCalendar with using KPIDate

Inline table with monthno starting at 1, and MasterMonth starting with 'Aug' an so on..

Can anyone help please, i'm pulling my hair out!!

Thx

[matt_crowther]

As a quick suggestion; use the method above where you create a straight table with the Months in column A and the order you wish to display them in column B.

Then when it comes to sorting in the chart / table use the dual() function in the Sort by Expression ie dual(column A, column B). As far as understand this sorts column A by the values in column B.

This is the method I've used to create Fiscal Year sort orders - of course the 'Month' column has to link back into your wider dataset.

Hope that helps.

Matt

[RickWild64]

I think the trouble is the Months from your inline table are really text, but the values of the Month() function are really numbers. QV displays the numeric values according to the value of the MonthNames variable, but doesn't treat them as proper text. Thus you get two distinct sets of values. Probably one set is left-aligned in the list box and the other is right-aligned?

The result is that it is more tricky than expected to control the sort.

I have a text version like Nimish suggested just for sorting, and a numeric version for calculations.