Sort Order

[upaliwije]

=if(I_DAY >= 0 and I_DAY <=15, '1-15 Days',

if(I_DAY >= 16 and I_DAY <= 25,'16-25 Days',

if(I_DAY > 25, '> 25 Days')))

I have used above calculated dimension in my pivot table and it appears as follows

Month     16-25 days   > 25 Days   1-15 days

but I want to sort the order it to appear as follows

Month     1-15 days   16-25 days   > 25 Days  

I tried to sort it but it wont work pls help me

[CELAMBARASAN · Accepted solution]

Try with Dual, we can sort any text to the need

Number defines the sorting order

=if(I_DAY >= 0 and I_DAY <=15, Dual('1-15 Days', 1),

if(I_DAY >= 16 and I_DAY <= 25, Dual('16-25 Days', 2),

if(I_DAY > 25, Dual('> 25 Days', 3) )))

[amit_saini]

Hi Upali,

Try  something like this:

load

    *,

    dual(if(Day >=0 and Day <= 7, '0-7 days',

    if(Day >=8 and Day <= 14, '8-14 days',

    if(Day >=15 and Day <= 21, '15-21 days',

    if(Day >=22 and Day <= 28, '22-28 days',

    if(Day>=29 and Day <= 60, '29-60 days',

    if(Day >=61 and Day <= 90, '61-90 days',

    if(Day >=91 and Day<= 120, '91-120 days',

    if(Day >121, 'Above 121 days')))))))),

    if(Day >=0 and Day <= 7, 10,

    if(Day >=7 and Day <= 14, 20,

    if(Day>=15 and Day <= 21, 30,

    if(Day>=22 and Day <= 28, 40,

    if(Day >=29 and Day<= 60, 50,

    if(Day >=61 and Day <= 90, 60,

    if(Day>=91 and Day <= 120, 70,

    if(Day >121, 80))))))))

    ) as DayAgeGroup;

Thanks,

AS

[#4]

Hi Upali

Use the match function in the sort tab as an expression

match(Interval,'1-15 Days','16-25 Days','>25 Days')

Thanks and regards

Padma

[upaliwije]

Thanks

[upaliwije]

Thanks