Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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