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

[Anonymous]

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

[avkeep01 · Accepted solution]

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]

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

[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

[trdandamudi]

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;

[Anonymous]

Hi Don Bosco,

  Find the Attachment. It may help you

-Jai