ASK.. Set range in value

[#1]

hello all, need helpp 😄

in my customer database has a [BirthDate] field.

to get customer age,

i use :

year(today() - year(BirthDate) as Age,

and, in pie chart

i use dimension [Age], and expresion [sum(sales)],

from that i can see Sales by Customer Age.

but the problem is,

the pie chart show in all age. not in range

the question is,

can i set range between age in pie chart?

i have already tried class function, CLASS(Age,10,'Age_Range')

but the result is not good enough, because the range between age is fix by 10.

i want to set a diferrent range in age,

this is my range between age,

>=80

60 - 79

40- 59

30-39

20-29

<=19

i have tried load it by INLINE too, but it doesn't work properly,

maybe wrong in syntax, plis help me

what is the INLINE LOAD syntax?

this is what i type,

LOAD * INLINE [

Age, Age_Range

>= 80, 80 Above

>=60<=79, 60 - 79

>=40<=59, 40- 59

>=30<=39, 30 - 39

>=20<=29, 20 - 29

<=19, Under 19

];

the syntax above won't work,

plis can somebody help?

regards...

Thank you before

[suniljain]

Raw3:
Load *,
(Today()-PostingDate) as Days,
// IF(('$(MaxDate)'-PostingDate)>0 AND ('$(MaxDate)'-PostingDate)<=15, '0-15',
IF((Today()-PostingDate)>0 AND (Today()-PostingDate)<=15, '0-15',
IF((Today()-PostingDate)>15 AND (Today()-PostingDate)<=30, '16-30',
IF((Today()-PostingDate)>30 AND (Today()-PostingDate)<=45, '31-45',
IF((Today()-PostingDate)>45 AND (Today()-PostingDate)<=60, '46-60',
IF((Today()-PostingDate)>60 AND (Today()-PostingDate)<=90, '61-90',
IF((Today()-PostingDate)>90 AND (Today()-PostingDate)<=180, '91-180',
IF((Today()-PostingDate)>180 AND (Today()-PostingDate)<=365, '6Month-1Year',
IF((Today()-PostingDate)>365 AND (Today()-PostingDate)<=730, '1Year-2Year',
IF((Today()-PostingDate)>730 AND (Today()-PostingDate)<=1095, '2Year-3Year',
IF((Today()-PostingDate)>1095 AND (Today()-PostingDate)<=1825, '3Year-5Year',
IF((Today()-PostingDate)>1825, 'Above-5Year'
))))))))))) as Out_Ageing
resident Raw2;
drop table Raw2;

[#3]

so.. i must type like this in edit script????

load

...

...

...

year(today() - year()BirrhDate) as Age,

if(year(today() - year()BirrhDate)<20,'Under 19',

if(year(today() - year()BirrhDate)>=20 and year(today() - year()BirrhDate)<=29,'20-29',

if( year(today() - year()BirrhDate)>=30 and year(today() - year()BirrhDate) <=39,'30 - 39',

if(year(today() - year()BirrhDate)>=40 and year(today() - year()BirrhDate)<=59, '40- 59',

if(year(today() - year()BirrhDate)>=60 and year(today() - year()BirrhDate)<=79, '60 - 79',

if(year(today() - year()BirrhDate)>=80,'80 Above'))))) AS AGE_RANGE

from ......

i can't use Age in there, because age isn't field,

is it??

plis correct me if i'm wrong

thank you all for your reply,

that is very kind of you,


Cheerss 😄

[suniljain]

Yes , You are right and Perfectly getting logic.

Age is not Field .

[#5]

thank you for all your help...

wish you all have a nice day 😄

[johnw]

Use the age() function instead of just subtracing years. If I was born on December 31, 1990, I am not 20 years old yet; I'm only 19. Since each new IF is under ELSE instead of something like a case statement, one of the AND conditions can be removed from each line. Another simplification is to do a preceeding load so that you only calculate the age once.

LOAD *
,if(Age<20,'Under 20'
,if(Age<30,'20-29'
,if(Age<40,'30-39'
,if(Age<50,'40-49'
,if(Age<60,'50-59'
,if(Age<70,'60-69'
,if(Age<80,'70-79'
,'80+'))))))) as "Age Range"
;
LOAD
...
age(today(),"Date of Birth") as Age
...

Edit: It's possible that avoiding the preceeding load would help performance though. We can still just calculate age once with a more complicated expression. This is one way. I probably wouldn't do it unless performance is a problem, though, since the nested if() is more clear and more robust. You can make this robust with a pick(1+match()), but that's even more complicated.

pick(floor(age(today(),"Date of Birth")/10),'Under 20','Under 20','20-29','30-39','40-49','50-59','60-69','70-79','80+','80+','80+','80+','80+','80+','80+','80+','80+')