topic Re: Avg of Date difference in QlikView

Avg of Date difference

Sk1 — Wed, 25 Nov 2020 16:16:04 GMT

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])

Re: Avg of Date difference

m_woolf — Wed, 08 Jan 2020 14:27:03 GMT

You might try calculated the date difference in script

Re: Avg of Date difference

dplr-rn — Wed, 08 Jan 2020 14:34:03 GMT

what is the error?

Re: Avg of Date difference

Sk1 — Wed, 08 Jan 2020 14:36:35 GMT

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

Re: Avg of Date difference

Sk1 — Wed, 08 Jan 2020 14:40:35 GMT

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

Re: Avg of Date difference

dplr-rn — Wed, 08 Jan 2020 14:44:18 GMT

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

Re: Avg of Date difference

martinpohl — Wed, 08 Jan 2020 14:52:51 GMT

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

Re: Avg of Date difference

Brett_Bleess — Wed, 05 Feb 2020 21:51:02 GMT

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