topic ASK.. Set range in value in QlikView

ASK.. Set range in value

Mon, 16 Aug 2010 03:57:09 GMT

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

ASK.. Set range in value

Mon, 16 Aug 2010 07:23:58 GMT

anybody help pliss...

thank you

ASK.. Set range in value

Mon, 16 Aug 2010 09:23:09 GMT

anybody help pliss...

thank you

ASK.. Set range in value

Mon, 16 Aug 2010 09:39:56 GMT

Hi,

You can create a new field 'Age Range' in your script as follows,

if(Age<20,'Under 19',if(Age>=20 and Age<=29,'20-29',if( Age>=30 and Age <=39, '30 - 39',if(Age>=40 and Age<=59, '40- 59',if(Age>=60 and Age<=79, '60 - 79','80 Above')))))

You can use the above field as dimension in the chart and get the desired output.

-Haneesh

ASK.. Set range in value

suniljain — Mon, 16 Aug 2010 09:49:07 GMT

Haneesh's Script will perfectly working. I used Same logic in Finance Application

ASK.. Set range in value

Mon, 16 Aug 2010 09:50:26 GMT

hello haneesh,

thank you for your reply,

but, where did i type this syntax?

if(Age<20,'Under 19',if(Age>=20 and Age<=29,'20-29',if( Age>=30 and Age <=39, '30 - 39',if(Age>=40 and Age<=59, '40- 59',if(Age>=60 and Age<=79, '60 - 79','80 Above')))))

in 'edit script' or in field expression, or where??

ASK.. Set range in value

Mon, 16 Aug 2010 09:54:58 GMT

hello sunin, where must i type that script?

ASK.. Set range in value

suniljain — Mon, 16 Aug 2010 09:59:21 GMT

Dear Friend,

There are two way to write this script

1) In Experession of Chart

2) Script Level

I will suggest you to write at Script Level.

SV:Re: ASK.. Set range in value

gandalfgray — Mon, 16 Aug 2010 09:59:38 GMT

In the script - in the same "Load" statement where you load the Age dimension

SV:Re: ASK.. Set range in value

Mon, 16 Aug 2010 10:19:44 GMT

Hi,

You should include this expression in the load statement where you load the Age field.

-Haneesh

SV:Re: ASK.. Set range in value

suniljain — Mon, 16 Aug 2010 10:21:06 GMT

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;

SV:Re: ASK.. Set range in value

Mon, 16 Aug 2010 10:22:09 GMT

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 😄

SV:Re: ASK.. Set range in value

suniljain — Mon, 16 Aug 2010 10:24:47 GMT

Yes , You are right and Perfectly getting logic.

Age is not Field .

SV:Re: ASK.. Set range in value

Mon, 16 Aug 2010 10:39:01 GMT

thank you for all your help...

wish you all have a nice day 😄

SV:Re: ASK.. Set range in value

johnw — Tue, 17 Aug 2010 00:01:35 GMT

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