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

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Team Name

Days 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.

Name

Count of ID that are < 7 Days

Count of ID that between the 8 - 14 Days range

Count of ID that between the 15 - 19 Days range

Count 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.

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

avkeep01 — Wed, 29 Nov 2017 13:10:03 GMT

Hi Don,

You're almost there. try the following lines:

<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)

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

avkeep01 — Wed, 29 Nov 2017 13:11:08 GMT

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)

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

PrashantSangle — Wed, 29 Nov 2017 13:14:00 GMT

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

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

Anil_Babu_Samineni — Wed, 29 Nov 2017 13:14:24 GMT

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

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

trdandamudi — Wed, 29 Nov 2017 13:26:17 GMT

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;

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

Anonymous — Wed, 29 Nov 2017 13:34:58 GMT

Hi Don Bosco,

Find the Attachment. It may help you

-Jai