displaying months in a different order - Fiscal year

[Davidoff]

hi all,

My companys 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.

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..

I've also tried an inline table with Months starting at Jan and MonthNo's to show which order to sort. Also made use of the 'dual(colA,ColB)' expression in the sort; still the same issue stands.

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

Thx

[#2]

David,

Don't give up on the Dual keyword in loadscript. I used an inline table like this to help with a report for a company whose fiscal year ran from July through June:

FISMONTH_DUAL:
Load dual (moname,monumber) as FIS_MONTH inline
[moname,monumber
Jul,01
Aug,02
Sep,03
Oct,04
Nov,05
Dec,06
Jan,07
Feb,08
Mar,09
Apr,10
May,11
Jun,12];

The data being loaded from the database had normal calendar month and calendar year dates in it. As part of the load I added a fiscal month and fiscal year field to the data:

Left join load
CAL_YEAR,
CAL_MONTH,
if(CAL_MONTH<7,CAL_MONTH+6,CAL_MONTH-6) as FIS_MONTH,
if(CAL_MONTH<7,CAL_YEAR,CAL_YEAR+1) as FIS_YEAR

The inline table helped make the month names show up correctly on charts.

[Davidoff]

Hi Tim,

thanks for your response. All of the options i have tried, including your one, do begin the chart with August (our fiscal start month), but the re-occuring problem i cannot solve is after displaying December data, January 2010 data shows!

So in other words i can get the chart to begin at August 2010 through to December, but then January 2010 to July shows, when it should show nothing, or Jan to July 2011.

I think i need some way of hooking the year into the sort order...but how does one accomplish this??

thx.

[#4]

I would think that you could add fiscal year to the dimension.

Or, I usually include a combined fiscal year and fiscal month field in the data as it is loaded even if I don't use it in a chart... so, 201101, 201102, 201103, etc., or 2011_01, 2011_02... Fields like that are easy to sort or use as a dimension. I have also used a field like 2011_01_Aug, 2011_02_Sep, etc... then it not only sorts correctly but it makes sure there is no confusion about calendar month versus fiscal month. It depends on what your report users are used to seeing.

[Davidoff]

Thanks again Tim,

yes that sounds like what i need. I have included this in the load; the data sorts lovely but i cannot get it to start at August! If I sort by expression (fiscalmonth from my inline table) it starts at August, but then shows January 2010 after December.

The only way i can see is to create an inline table with 200808, 200809...etc to the end of time.

Is there not a way i can use a wildcard in the expression sort to say 'any year & inline table month' ??

thanks,

David

[#6]

You could use a Calculated Dimension with an expression that might look like this: FISCAL_YEAR & '-' & FISCAL_MONTH

You could use an expression like that for sorting too, but I suspect your chart needs it as a dimension.

[Davidoff]

I'm sorry to say that this doesnt work either; i added the calculated dimension and removed the original '=Month(MonthYear)' dimension, which totally screwed the pivot table; i got just two columns.

If i add the calcd dimension and leave the original alone i get multiple columns under each month for the years, which are still wrong!

Don't get me wrong, i love Qlikviews functionality and the design power, but not to be able to do something as simple as ordering a chart/ table by fiscal months is frankly pathetic.

[johnw]

Here's a basic calendar with both an ActualMonth and a FiscalMonth. The FiscalMonths sort correctly. I'm unclear what actual charts you're using, so don't know if this will solve your problem. If it does not, can you post an example file demonstrating the problem you're having?

[Davidoff]

Hi John,

thanks for your response.

I'm using a pivot table. But i have now solved the issue by creating the fiscal months and years in SQL and pumping that into QV. I then sort by fiscal year & fiscal month, and use a nested IF statement to replace the fiscal month numbers with the correct month name!

It would be great if in QV a parameter could be set stating the beginning month to sort from. I think they missed a trick here.

Regards,

David