Sorting calculated dimension

[#1]

Hi All,

I have written calculated dimension as -

if(interval(date(vDate)-date([OutStd.Base Date]),'dd')<=30,'0-30',
      if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>30 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=60,'31-60',

           if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>60 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=90,'61-90',

                    if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>90 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=120,'91-120',
                              if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>120 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=150,'121-150',

                                     if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>150 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=180,'151-180',

                                               if(interval(date(vDate)-date([OutStd.BaseDate]),'dd')>180 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=365,'181-365','Above 365')))))))

 

When I create a chart the buckets are not in ascending order. I require the order as 0-30,31-60, 61-90,91-120,121,150,151-180...... 

How do I sort this??

Thank You

[alexandros17]

In the Sort Tab, copy in expression textbox the expression you have written

doing:

if(interval(date(vDate)-date([OutStd.Base Date]),'dd')<=30,1, and so on in this way each intervall will be assigned to a number and with ascending or descending sort you will be available to sort

Hope it helps

[nizamsha · Accepted solution]

use dual when u use dual it will display in text but sorting is done in ascending order or descending as u wish u will get the sort order correctly

if(interval(date(vDate)-date([OutStd.Base Date]),'dd')<=30, dual('0-30',1),
      if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>30 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=60, dual('31-60',2),

           if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>60 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=90, dual('61-90',3),

                    if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>90 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=120,dual('91-120',4),
                              if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>120 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=150, dual('121-150',5),

                                     if(interval(date(vDate)-date([OutStd.Base Date]),'dd')>150 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=180, dual('151-180',6),

                                               if(interval(date(vDate)-date([OutStd.BaseDate]),'dd')>180 and interval(date(vDate)-date([OutStd.Base Date]),'dd')<=365,dual('181-365',7),dual( 'Above 365',8))))))))

[nizamsha]

use dual at the backend

[jonathandienst]

Hi

You can also simplify this by removing the redundant If clauses and formatting instructions:

if(vDate - [OutStd.Base Date] <= 30, Dual('0-30', 0),

if(vDate - [OutStd.Base Date] <= 60, Dual('31-60', 31),

if(vDate - [OutStd.Base Date] <= 90, Dual('61-90', 61),

if(vDate - [OutStd.Base Date] <= 120, Dual('91-120', 91),

if(vDate - [OutStd.Base Date] <= 150, Dual('121-150', 121),

if(vDate - [OutStd.Base Date] <= 180, Dual('151-180', 151),

if(vDate - [OutStd.Base Date] <= 365, Dual('181-365', 181), Dual('Above 365', 365))))))))

HTH

Jonathan

[maxgro]

I think you can

replace   '0-30'

with        dual('0-30', 0)

and so in in your calculated dimension  (below an example)

and in sort tab use numeric sort

=if(interval(vDate-d,'dd')<=30,dual('0-30',0),

      if(interval(vDate-d,'dd')>30 and interval(vDate-d,'dd')<=60,dual('31-60',1),

           if(interval(vDate-d,'dd')>60 and interval(vDate-d,'dd')<=90,dual('61-90',2),

                    if(interval(vDate-d,'dd')>90 and interval(vDate-d,'dd')<=120,dual('91-120',3),

                              if(interval(vDate-d,'dd')>120 and interval(vDate-d,'dd')<=150,dual('121-150',4),

                                     if(interval(vDate-d,'dd')>150 and interval(vDate-d,'dd')<=180,dual('151-180',5),

                                               if(interval(vDate-d,'dd')>180 and interval(vDate-d,'dd')<=365,dual('181-365',6),

                                               dual('Above 365',100))))))))