Sorting of Month names in pivot

[#1]

Hi All,

I have two year month names concatenated with Month names in columns of a pivot table i.e. 24 columns.

Dimesion is like :-

year_cd&'Y-'&Rptg_Mth

which gives us columns like

PY-Jan

PY-Feb

.

.

.

Py-Dec

Cy-Jan

Cy-Feb

.

.

..

Cy-Dec.

Every month sequence of columns changes w.r.t current month. For ex:

PY-Feb

.

.

.

Py-Dec

PY-Jan

Cy-Feb

.

.

..

Cy-Dec

Cy-Jan

and this continues...

Please guide me how should I handle sorting for this case??

[Sokkorn]

Hi,

I think sort by expression may do this. But if you can share sample file then may better to understand your issue.

Regards,

Sokkorn

[hic]

The best way is to create a dual field from your year-month. A dual field is a field with both a display text and a number, and it can then be sorted numerically. E.g.

dual(year_cd&'Y-'&Rptg_Mth, DateNumber) as YearMonth

The DateNumber can be created using MakeDate() or Date#(), depending on how your data looks.

[#4]

Hi Henric,

I read about dual and think that this can be a good option but m not that clear about second argument i.e. DateNumber. Please elaborate.

Also, in refrence manual there is one example given for dual function which involves creation of an inline view for giving an order to my column values. I have tried this:-

dual(year_cd&'Y-'&Rptg_Mth, numrep) as YearMonth

Load * INLINE [

year_cd&'Y-'&Rptg_Mth, numrep

PY-Jan,0

PY-Feb,1

.

.

.

PY-Dec,11

CY-Jan,12

.

.

.

CY-Dec,23

But I am doubtful that this will give desired results as I need to handle PY strings separately from CY strings while this will handle both as same string.Please see required updated sequence of columns of every month.

Please help!!

Thanks,

Kirti

[hic]

It depends on how your raw data looks. If all you have is year_cd {P, C} and Rptg_Mth {Jan, Feb, ...} then I suggest a

Let vThisYear = Year(Today()) ;

Load

dual(

  year_cd & 'Y-' & Rptg_Mth,

  MakeDate(

   if(year_cd='C',

    $(vThisYear),  // should be 2012

    $(vThisYear)-1 // should be 2011

   ),

   Month( Date#(Rptg_Mth,'MMM')) // will interpret the month name and assign correct month number

   )

  ) as Year_Month,

...

From DataTable ;

But if you already have a numeric date in the data, the expression will be simpler.

HIC

[#6]

Hi,

I have tried your solution, it is giving me sort order of PY-Jan, PY-Feb....PY-Dec,CY-Jan,CY-Feb....CY-Dec. But I need something different. Actually sorting needs to be done on the basis of a field which is RPTG_MTH_NBR. This field has numeric value like this:-

RPTG_MTH_NBR     RPTG_YEAR_CD     RPTG_YEAR_MTH_NAME

1                              P                           March

1                              C                           March

2                              P                           April

2                              C                           April......... continues....

My dimension is like:-

( RPTG_YEAR_CD&'Y-' mid(RPTG_YEAR_MTH_NAME,1,3)

which gives me PY-Jan,PY-Feb etc.

I need sort order as per RPTG_MTH_NBR but all clumns of RPTG_YEAR_CD = P should be first and then all

RPTG_YEAR_CD = C should follow them. For this month, required sort order is like:-

PY-Mar,PY-Apr, ...........PY-Dec,PY-Jan,PY-Feb, CY-Mar,CY-Apr, ...........CY-Dec,CY-Jan,CY-Feb.

For this I need to first sort at RPTG_YEAR_CD as Text Z->A and second sort at RPTG_MTH_NBR. But I am not to find order of sorting in single dimsion.

Please help.

Thanks,

Kirti