Age calculation with variable input date

[huixuan_tan]

Hi all,

I am trying to calculate the age of a group of students with an input date variable. The dataset of birthdates of the students are as follows:

Birthdate

12/04/2000

27/11/1995

3/05/2002

The codes i have tried are:

age(input_var_date, date(Birthdate))

age(date(input_var_date), date(Birthdate))

age(date(input_var_date), Birthdate)

The results are all null.

Similarly, I have changed the input variable into 3 inputs: 

vDay, vMonth, vYear

and have calculated with the simple if condition:

if(vMonth<Birthdate.Month, vYear-Birthdate.Year-1, vYear-Birthdate.Year)

The results were also null. The issue is determined to be that the Birthdate is a dataset with multiple values but the variable input(s) is only 1 set of data. If there are any approach to this problem please do suggest and help!! Thank you!!

[sunny_talwar]

what is input_var_date? A variable?

[huixuan_tan]

Yes a date variable for user to enter any date they like

[mfarsln]

Hi,

You can try to add your own age function to the script. Then use this script on expressions.

Function which gives you the month differences between two dates is like this;

SET MonthDiff =  ((year($1)*12)+month($1)) - (((year($2)*12)+month($2)));

After you add this to the script, you can use this on front-end like any other qlik functions.

=$(MonthDiff('$(inputDate)',date))

---

[anseglko]

Have you tried adding $() around variables?

age($(input_var_date), date(Birthdate))

[sunny_talwar]

Assuming they will enter the date in the format D/M/YYYY, try this

Age(Date#(input_var_date, 'D/M/YYYY'), Birthdate)

[kaanerisen · Accepted solution]

Hi,

You can use makedate function with the input parameters to create the date string and calculate the age by using age function.

 

Age Expression:

age(MakeDate($(vYear),$(vMonth),$(vDay)),BD_DATE)

I used "qs-variable" extension to be able to create dynamic array for input values on drop-down lists. If you just want to type a date string you can use "variable input" extension comes with qlik dashboard bundle.

The sample app is attached. You can check it out. It may need little tweak according to your app's date format which is set on script side.

Hope it helps...