Problem in Period Dimension Sorting

[#1]

Hi All,

   I want to sort my Period dimension which contains month,quarter,year in a single field

Sorting should be like

Jan 2011

Feb 2011

mar 2011

Q1 2011

Apr 2011

May 2011

Jun 2011

Q2 2011

Jul 2011

Aug 2011

Sep 2011

Q3 2011

Oct 2011

Nov 2011

Dec 2011

Q4 2011

YTD 2011

Jan 2012

so on...

At a time i will show 2 years

I have written in my Sort expression

WildMatch(Report_Period,
'YTD '&vMaxYear

it is taking so much of time can we do this in back end

[#2 · Accepted solution]

Hi,

I think one solutions for this would be to have a separate "Master" table containing all these values.

Maybe with an additional column for sort value.

If you create the table for example in an Excel-sheet, you could use Load-Order in the sort options.

The second solution is to use the Sort-Value as a second column of the "Master-table".

Join the "Master-"Table via your Period-field.

Period          Sort

Jan 2011     1

Feb 2011     2

mar 2011     3

Q1 2011     4

Apr 2011     5

May 2011     6

Jun 2011     7

Q2 2011     8

Jul 2011     9

Aug 2011     10

Sep 2011     11

...

regards Martin

[#3]

Hi Martin,

   Thanks a lot for the reply I did the same thing I have created an excel sheet and linked it with my Period and maintained a seperate field for sort . But along with these values by Using Pick  and match i am having YTD Change,% YTD Growth,YTD Group Like below

=

Pick(Match(KPI,1,2,3,4), Report_Period

,'YTD Group','% YTD Growth','YTD Change')

AS Report Period is Field it is sorting properly these extra 3 things are sorting with text but i need them exactly how i mention in the dimension

Can u suggest me any work around

Thanks & Regards

Swetha

[#4]

Hi Swetha,

use the following load..

LOAD dual (Period, Sort) as Period

INLINE [    

Period, Sort    

Jan 2011, 1    

Feb 2011, 2    

Mar 2011, 3    

Q1 2011, 4    

Apr 2011, 5    

May 2011, 6    

Jun 2011, 7    

Q2 2011, 8 ];

After that the Period is sorted the way you want it..

Regards

Martin