topic Re: Age Bucket Expression Question in App Development

Age Bucket Expression Question

Anonymous — Thu, 25 Oct 2018 20:58:13 GMT

Hi,

I am wondering what is wrong with my expression. The IF statement works with just the first part, but if I add in the second parameters, I get the error "Error in expression: If takes 2-3 parameters". Can anyone help me to correct the syntax so that I have the specified 3 buckets?

=
IF

(

(Round( ((TODAY())-DOB)/365) )            >=6

           and

(Round( ((Today())-DOB)/365) )            <=17

)

,'6-17',

IF
(

(Round( ((TODAY())-DOB)/365) )            >17

           and

(Round( ((Today())-DOB)/365) )            <=65

)

,'18-65','OTHER'

Re: Age Bucket Expression Question

andrey_krylov — Fri, 26 Oct 2018 04:35:34 GMT

Hi Isabel. Maybe this

= IF(

(Round( ((TODAY())-DOB)/365) )            >=6
           and
(Round( ((Today())-DOB)/365) )            <=17

,'6-17',

IF(

(Round( ((TODAY())-DOB)/365) )            >17
           and
(Round( ((Today())-DOB)/365) )            <=65

,'18-65', 'OTHER'))

Re: Age Bucket Expression Question

Anonymous — Fri, 26 Oct 2018 14:00:02 GMT

Yes, that's it, Perfect, Thanks!

Re: Age Bucket Expression Question

Anonymous — Fri, 26 Oct 2018 14:12:09 GMT

There were a few misplaced parentheses. The below should work.

=IF(Round(((TODAY())-DOB)/365)
>=6 and Round(((Today())-DOB)/365) <=17 ,'6-17',
IF(Round(((TODAY())-DOB)/365) >17 and Round((Today()-DOB)/365) <=65 ,'18-65','OTHER'))

Also, qlikview has a function for everything:

=IF(Age(TODAY(),DOB) >=6 and Age(TODAY(),DOB) <=17 ,'6-17',IF(Age(TODAY(),DOB) >17 and Age(TODAY(),DOB) <=65 ,'18-65','OTHER'))

To take it one step further you can do an interval match in the load to calculate the buckets. If you'd like an example of that just reply and let me know.