Avg of Date difference

[Sk1]

I have been trying to work out average of the difference between 2 dates and the expression doesn’t seem to work, I thought probably  its because the dates are in different formats. so I converted the text one to date in my expression but this still doesn’t work. Do you know what I am doing wrong?

Avg({<CapabilityStatus_Name={'Confirmed'},[Job Group]={'HCP Non Doc'}>}Date(Date#([EmployeeCapabilities_ApprovedDate],'YYYYMMDD'),'DD/MM/YYYY')-[Emp Training Start Date])

[m_woolf]

You might try calculated the date difference in script

[dplr-rn]

what is the error?

[Sk1]

Doesn't give me any output. The results are blank

[Sk1]

But when I replace Date(Date#([EmployeeCapabilities_ApprovedDate],'YYYYMMDD'),'DD/MM/YYYY') with todays date Today(), I get a value

[dplr-rn]

i would say check the column type of EmployeeCapabilities_ApprovedDate in data model viewer.

and make sure it is a date field which loading from script itself rather than on the expression.

and as Mwoolf suggested. perform the difference in the script if possible and then take average of that difference field

[martinpohl]

Hi,

I think a date-expression in a set analysis doesn't word so try to convert the date in script.

also, if you want to calculate an average, be sure that there is only one combination of the two dates.

It is better to calculate the difference in script and do the average in expression.

Regards

[Brett_Bleess]

Just going to toss out the following Design Blog post too, it might give you some further options possibly, not sure.

https://community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

Here is one on dates not working too:

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

If you want to dig further as there are some other date posts too, use the following base link for the area:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett