Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Yes. It worked. Thank you.
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)
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
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
Yes, In Script level we can use this. Thank you for the 2nd approach. In my senario i am looking at UI level.
replace '1 - 30 days' with dual('1 - 30 days', 1) and so on
and sort by number
Yeah. But the AgeBand is '1 to 30 days'. sorry i mentioned like 1 - 30 days in previous post.
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