Month Sorting Issue

[Anonymous]

Hi Everyone,

I have a [cons_month] field in which there are 15th no means ( 1 to 15 ) from 1 to 12 means Jan to Dec month and greater than 12 means Supplementary month (Supp_month) for this i wrote following script

if([Cons Month] = 1,'Jan',

        if([Cons Month] = 2,'Feb' ,

         if([Cons Month] = 3,'Mar' ,

          if([Cons Month] = 4,'Apr',

           if([Cons Month] = 5,'May',

            if([Cons Month] = 6,'Jun',

             if([Cons Month] = 7,'Jul',

              if([Cons Month] = 8,'Aug',

               if([Cons Month] = 9,'Sep',

                if([Cons Month] = 10,'Oct',

                 if([Cons Month] = 11,'Nov',

                  if([Cons Month] = 12,'Dec',

                   if([Cons Month] >12,'Sup_month'))))))))))))) as FactMonth,

This is working fine but when we short this month then its not giving proper ordering of month when we short through

sort tab - load original order then its giving proper order but Dec month is coming before Nov month . like below-

Supp_month, apr, may, jun, jul, aug, sep, oct, dec, nov, jan, feb, mar.

I'm not getting why its coming like that .

Please anyone give me some idea.

Thanks

[anbu1984]

Try sorting on [Cons Month]. If its not working can you post sample app.

[jagan]

Hi,

Try like this

If([Cons Month]

Dual(Pick(Wildmatch([Cons Month], 1,2,3,4,5,6,7,8,9,10,11,12, '*')

     , 'Jan', 'Feb', 'Mar', 'Apr', .....................'Dec', 'Supp_Month'), [Cons Month]) AS FactMonth

Hope it helps you.

Regards,

Jagan.

[jagan]

Now use this as dimension and in sort tab select Numeric Value.

Hope this helps you.

Regards,

Jagan.

[#5]

Hello Dhiraj,

May be an issue with the numbers. I almost had the same problem with weeknumbers and solved the problem by using this formula in the master calendar: right('00' & Week(TempDate),2) as Week

Hope this will help you a bit.

Regards,

Martin

[padmanabhan_ram]

HI,

In the sort there will be an option Expression, select the [cons Month] field and ascending or descending with respect to your requirement and it should work.

Please let me know if you have nay concern.

Thanks,

Padmanabhan

[Anonymous]

Hi jagan Mohan,

Thanks for the reply if condition which u r wrote is not completed in condition place what is need to write.

thanks

[jagan]

Hi,

Sorry for the typo mistake

LOAD

*,

Dual(Pick(Wildmatch([Cons Month], 1,2,3,4,5,6,7,8,9,10,11,12, '*')

     , 'Jan', 'Feb', 'Mar', 'Apr', .....................'Dec', 'Supp_Month'), [Cons Month]) AS FactMonth

FROM DataSource;

No need of If(), instead we are using Pick.

Regards,

Jagan.

[tresesco]

Or, a bit simpler solution could be like:

Load

          Month(Date#([Cons Month], 'MM')) as NewMonth

[gandalfgray]

Hi Dhiraj

As always there's more than one way to do it.

I would go for creating a mapping table, and then use ApplyMap function.

(in this case where you want to sort in numerical order, with a Dual surrounding the ApplyMap - then in your gui-tables specify Numeric value sort))

like this:

// Create the mapping table

num2name:

Mapping

LOAD * INLINE [

    F1, F2

    1, Jan

    2, Feb

    3, Mar

    4, Apr

    5, May

    6, Jun

    7, Jul

    8, Aug

    9, Sep

    10, Oct

    11, Nov

    12, Dec

];

...

LOAD ...

Dual(ApplyMap('num2name',[Cons Month],'Sup_month'), [Cons Month]) As [Fact Month],

...

It's a matter of taste of course, but personally I think this is much easier to read and understand then nested if's.

I think it's also more efficient.

/gg