Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
donbosco_l
Contributor II
Contributor II

How to count day range and bucket them in different classes.

IDTeam NameDays since ID is open

 

1111
2222
3333
4444
5555
6666
7777
8888
9999
11110
12221
13332
14443
15554
16665
17776
18887
19998
21109
22220
23331
24442
25553
26664
27775
28886
29997
31108
32219
33330
34441
35552
36663
37774
38885
39996
41107
42218
43329

 

Apple
Orange
Turtle
Lion
tiger
Goat
Dog
Cat
Wolf
Rat
Bat
Ball
Jim
Apple
Orange
Turtle
Lion
tiger
Goat
Dog
Cat
Wolf
Rat
Bat
Ball
Jim
Apple
Orange
Turtle
Lion
tiger
Goat
Dog
Cat
Wolf
Rat
Bat
Ball
Jim

 

4
8
27
28
77
55
20
7
12
27
7
1
13
13
26
1
37
76
1
2
22
50
78
7
7
2
1
22
6
1
1
1
1
1
1
1
1
1
27

I am trying to bucket the teams in 4 different ranges by the number of days the ID has been open. Desired result is below.

NameCount of ID that are < 7 DaysCount of ID that between the 8 - 14 Days rangeCount of ID that between the 15 - 19 Days rangeCount of ID that are  > 21 Days

 

Apple
Orange
Turtle
Lion
tiger
Goat
Dog
Cat
Wolf
Rat
Bat
Ball
Jim

What I am using: The following brings back values, but they are not accurate.

<7 Days:  =count((if( Days_since_ID_is_open <= '*7*', Days_since_ID_is_open)))

8 - 14 Days : =count(DISTINCT(if(Days_since_ID_is_open>= '*8*' and (Days_since_ID_is_open<='*14*'), Days_since_ID_is_open)))

15 - 20 Days : =count(DISTINCT(if(Days_since_ID_is_open>= '*15*' and (Days_since_ID_is_open<='*20*'), Days_since_ID_is_open)))

>21 Days: =count(DISTINCT(if(Days_since_ID_is_open <= '*21*',Days_since_ID_is_open)))

Please advise if this is correct.

The other question I have is that I am not sure if the values under Days_since_ID_is_open is viewed as number or text. If it is text, how do I convert that to numbers.

1 Solution

Accepted Solutions
avkeep01
Partner - Specialist
Partner - Specialist

Hi Don,

You're almost there. try the following lines:

Wh

<7 Days:  =count({$<Days_since_ID_is_open ={">=0 <=7"}>}ID)

8 - 14 Days : =count({$<Days_since_ID_is_open ={">=8 <=14"}>}ID)

15 - 20 Days : =count({$<Days_since_ID_is_open ={">=15 <=20"}>}ID)

>21 Days: =count({$<Days_since_ID_is_open ={">=21"}>}ID)

View solution in original post

6 Replies
avkeep01
Partner - Specialist
Partner - Specialist

Hi Don,

You're almost there. try the following lines:

Wh

<7 Days:  =count({$<Days_since_ID_is_open ={">=0 <=7"}>}ID)

8 - 14 Days : =count({$<Days_since_ID_is_open ={">=8 <=14"}>}ID)

15 - 20 Days : =count({$<Days_since_ID_is_open ={">=15 <=20"}>}ID)

>21 Days: =count({$<Days_since_ID_is_open ={">=21"}>}ID)

avkeep01
Partner - Specialist
Partner - Specialist

The other question I have is that I am not sure if the values under Days_since_ID_is_open is viewed as number or text. If it is text, how do I convert that to numbers.

NUM(Days_since_ID_is_open)

PrashantSangle

take Team Name as dimension

expression

1: count({<days_since_ID_Open={"<7"}>}distinct ID)

2: count({<days_since_ID_Open={">7<15"}>}distinct ID)

3: count({<days_since_ID_Open={">14<21"}>}distinct ID)

4: count({<days_since_ID_Open={">20"}>}distinct ID)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Anil_Babu_Samineni

What I am using: The following brings back values, but they are not accurate.

<7 Days:  =count(if( Days_since_ID_is_open <= 7, ID))

8 - 14 Days : =count(DISTINCT if(Days_since_ID_is_open>= 8 and (Days_since_ID_is_open<=14), ID))

15 - 20 Days : =count(DISTINCT if(Days_since_ID_is_open>= 15 and (Days_since_ID_is_open<=20), ID))

>21 Days: =count(DISTINCT if(Days_since_ID_is_open >= 21,ID))

Please advise if this is correct.

The other question I have is that I am not sure if the values under Days_since_ID_is_open is viewed as number or text. If it is text, how do I convert that to numbers.

Edited in the comment itself

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
trdandamudi
Master II
Master II

One way to do in the script is as below:

ataSource:
Load * Inline [
ID, Team Name, Days since ID is open
1111,Apple,4
2222,Orange,8
3333,Turtle,19
4444,Lion,15
6666,Lion,4
5555,Tiger,25
18887,Lion,4

]
;

Data:
Load *,
If([Days since ID is open]<7,'Count of ID that are < 7 Days',
If([Days since ID is open]=8 and [Days since ID is open]<=14,'Count of ID that are between the 8-14 Days range',
If([Days since ID is open]=15 and [Days since ID is open]<=19,'Count of ID that are between the 15 - 19 Days range',
If([Days since ID is open]>21,'Count of ID that are > 21 Days')))) as Days_1
Resident DataSource;


Drop Table DataSource;

albi_prakash
Creator II
Creator II

Hi Don Bosco,

  Find the Attachment. It may help you

-Jai