
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorting calculated dimension
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))))))))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))))))))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
use dual at the backend


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))))))))
