Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to created a bucket but it's not working as I thought.
The idea is set ranges, but not excluding the other options. For example, I have a field called Days I have set the following:
If (Day =< 30, '1 to 30 Days',
If (Day > 30, '30 + Days',
If (Day > 45, '45 + Days',
If (Day > 60, '60 + Days',
If (Day > 90, '90 + Days',)))))
The problem here is if I want to have all above 30, I need to select 30 + Days, 45 + Days, 60 + Days and 90 + Days.
What I want is if I select 30 + Days it should include the other selections and no the range between 30 and 45.
This is in a List Box with field as expression.
Can you please help me with this?
another approach could be using cross table
Data:
LOAD *,
RowNo() as Key,
if(Days<=30,dual('1 to 30 Days',1)) as [Bucket<=30],
if(Days>30,dual('30 +Days',2)) as [Bucket>30],
if(Days>45,dual('45 +Days',3)) as [Bucket>45],
if(Days>60,dual('60 +Days',4)) as [Bucket>60],
if(Days>90,dual('90 +Days',5)) as [Bucket>90]
INLINE [
Days
10
20
30
13
56
76
45
89
34
44
13
88
92 ];
Bucket:
CrossTable(Value,Bucket)
LOAD Key,
[Bucket<=30] ,
[Bucket>30],
[Bucket>45],
[Bucket>60],
[Bucket>90]
Resident Data;
DROP Fields Value;
// DROP Fields [Bucket<=30],[Bucket>30], [Bucket>45],[Bucket>60],[Bucket>90] ;
You can drop the Bucket fields if not needed.
Try this
If (Day <= 30, '1 to 30 Days',
If (Day < 45, '30 + Days',
If (Day < 60, '45 + Days',
If (Day < 90, '60 + Days',
If (Day >= 90, '90 + Days',)))))
OR this? Typo error sunny with comma
If (Day <= 30, '1 to 30 Days',
If (Day >30 and Day <= 45, '30 + Days',
If (Day >45 and Day <= 60, '45 + Days',
If (Day >60 and Day <= 90, '60 + Days',
If (Day > 90, '90 + Days')))))
Did you try the solution I posted on your old thread ?
Nope, probably doesn't like you
The problem doing this is that if we take number 50 for example should be included in both filters
30+ days and 45 + days
If we use if() then a number will only be assigned to one filter
Hahaha
Does that give any error? I know its a typo, but I feel like QlikView still doesn't throw any error, but I might be wrong
Anil and Sunny, that didn't work. I am looking for accumulative, I mean if I have for example these records:
1
4
31
35
45
48
60
65
90
95
110
When I select 30 + Days it should bring everything except by 1 and 4
When I select 45 + Days, it should include 45, 48, 60, 65, 90, 95 and 110
When I select 60 + Days, it should include 60,65,90,95 and 110
and so...
Is it possible to do it? I have tried with or/and conditions like
if (day >30 or day >45 or day >60 or day >90, etc
but this didn't work.
I am sorry Vineeth, I didn't understand it.