Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

sorting

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.

1 Solution

Accepted Solutions
Josh_Good
Employee
Employee

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

View solution in original post

10 Replies
Josh_Good
Employee
Employee

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

Not applicable
Author

Yes. It worked. Thank you.

maxgro
MVP
MVP

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)

MK_QSL
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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

maxgro
MVP
MVP

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

and sort by number

Not applicable
Author

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

maxgro
MVP
MVP

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