Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
ID | Team Name | Days since ID is open | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
|
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 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
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.
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)
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)
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)
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
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
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;
Hi Don Bosco,
Find the Attachment. It may help you
-Jai