topic Re: Sorting calculated dimension in QlikView

Sorting calculated dimension

Tue, 20 May 2014 06:44:26 GMT

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

Re: Sorting calculated dimension

alexandros17 — Tue, 20 May 2014 06:55:08 GMT

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

Re: Sorting calculated dimension

nizamsha — Tue, 20 May 2014 06:55:59 GMT

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))))))))

Re: Sorting calculated dimension

nizamsha — Tue, 20 May 2014 06:57:42 GMT

use dual at the backend

Re: Sorting calculated dimension

jonathandienst — Tue, 20 May 2014 07:00:34 GMT

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

Re: Sorting calculated dimension

maxgro — Tue, 20 May 2014 07:18:38 GMT

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))))))))