topic Re: sorting in QlikView

sorting

Tue, 18 Nov 2014 18:45:56 GMT

Hi there,

I have a dynamic calculated dimensio(Age band)n in bar chart.The values are N/A, 1 to 30 days, 31 to 90 days, 91 to 180 days, 181 to 365 days, 366 days plus based on Age field. N/A will capture -ve values of Age. Iwant to order the data in bar chart based on ageband in the same way mentioned above. But unable to sort by using sort tab options. Can any one help? Good thing is the ageband values are fixed. No additional values will be added.

Re: sorting

Josh_Good — Tue, 18 Nov 2014 19:57:05 GMT

Hi Jhansi,

You can sort on the minimum value for each age band. An expression like Min(Age) would work (assuming you are generating your dimension from a field called "Age").

-Josh

Qlik

Re: sorting

Tue, 18 Nov 2014 20:02:08 GMT

Yes. It worked. Thank you.

Re: sorting

maxgro — Tue, 18 Nov 2014 20:05:29 GMT

you can use the dual function in the dynamic dimension and then sort by number

example, in bold the number used in sort

=aggr(if(sum(Expression1)>100000, dual('>100000', 100),

if(sum(Expression1)>90000, dual('>90000', 20),

if(sum(Expression1)>80000, dual('>80000', 15),

if(sum(Expression1)>60000, dual('>60000', 14),

if(sum(Expression1)>30000, dual('>30000', 13),

if(sum(Expression1)>0, dual('>0', 5)

)))))),

Dim2)

Re: sorting

MK_QSL — Tue, 18 Nov 2014 20:17:01 GMT

You can use dual function while creating the age band... something like below

Load

If(Age <= 0, Dual('N/A',0),

If(Age > 0 and Age <= 30, Dual('1 to 30',1),

If(Age > 30 and Age < 90, Dual('31 to 90', 2),

If(Age > 90 and Age <= 180, Dual('91 to 180', 3),

If(Age > 180 and Age <= 365, Dual('181 to 365', 4),

Dual('365 +',5)))))) as [Age Band];

Load

ID,

Date,

Interval(Today()-Date,'D') as Age

Inline

[

ID,Date

1,01/11/2014

2,05/11/2014

3,15/05/2014

4,01/02/2014

5,16/08/2014

6,25/08/2014

7,30/10/2014

8,17/11/2014

9,01/01/2012

10,26/12/2012

11,15/04/2014

12,25/12/2014

13,10/09/2013

14,12/06/2011

15,15/10/2014

];

Now create a Bar chart

Dimension

[Age Band]

Expression

COUNT(Distinct ID)

Sort Tab

Sort By Age Band Ascending or Descending Order

Re: sorting

Tue, 18 Nov 2014 20:19:25 GMT

Hi Massimo,

In the given solution Sum(Expression1) means the derived filed(AgeBand) expression or the Fact expression?

Dynamically calculated filed(AgeBand) expression =

if(Age>0 and Age<=30,'1 - 30 days',if(Age>30 and Age<=90, '31 - 90 days',if(Age>90 and Age<=180, '91 - 180 days',if(Age>180 and Age<=365, '181 - 365 days',if(Age>365,'366 days plus','N/A')))))

both the cases the value of Sum(Exp) will be always changing based on selection of other fields..!

Thanks,

Jhansi

Re: sorting

Tue, 18 Nov 2014 21:29:28 GMT

Yes, In Script level we can use this. Thank you for the 2nd approach. In my senario i am looking at UI level.

Re: Re: sorting

maxgro — Tue, 18 Nov 2014 21:45:17 GMT

replace '1 - 30 days' with dual('1 - 30 days', 1) and so on

and sort by number

Re: sorting

Tue, 18 Nov 2014 21:59:13 GMT

Yeah. But the AgeBand is '1 to 30 days'. sorry i mentioned like 1 - 30 days in previous post.

Re: sorting

maxgro — Tue, 18 Nov 2014 22:05:00 GMT

Manish expression should works also in the UI

You show a text, '1 to 30' '31 to 90'

You sort by number 1 2

If(Age <= 0, Dual('N/A',0),

If(Age > 0 and Age <= 30, Dual('1 to 30',1),

If(Age > 30 and Age < 90, Dual('31 to 90', 2),

If(Age > 90 and Age <= 180, Dual('91 to 180', 3),

If(Age > 180 and Age <= 365, Dual('181 to 365', 4),

Dual('365 +',5))))))

If it doesn't work, post your .qvw, thanks

Re: sorting

MarcoWedel — Tue, 18 Nov 2014 22:56:23 GMT

Hi,

maybe this helps:

Custom Filter(unusual range)

This solution uses the dual function to create underlying numerical values for unequal sized buckets so that sorting needs no special sorting expressions.

regards

Marco