Need your help urgently on one of my form attached test_tool.xlsx (16.5 KB)
I am having two issues on this form:
I did a calculation as: format-date(today(), '%Y%m%d')+(50-${age}) and the result I am expecting is, when the age of the person is 50 or more, then the service1 will expire. My expected output should be if the person's age is 20 today then it will expire in 2050. My calculation is adding up with the date value, not with year.
2nd problem, service2 -service5 have expiry dates. I need to display an expiry date based on the date the service was provided to the client/patient. For example service 2 should expire 4 weeks after the service was provided to the patient and service3 should expire after 3 months of the service was provided to the patient.
I need to resolve this as soon as possible. Your support will be highly appreciated.
For the second question the idea is to get the duration of each service and to compute it with the subscription date.
I know how to do it from an external csv choice list (with pulldata() ) but I think it is possible from the choices sheet.
I found this to get a value associated to the choices in choices sheet but I can't get it run : Get custom data from choices sheet for calculation - #2 by MattFoy
Secondly, I checked the link you shared; however, I could not figure out how do I achieve on my test form. For example, if service2 expires 3 weeks after the date of service received or service3 expires 1 year after from the date of service received. How do I need to calculate that?
Your support will be highly appreciated,
Thanks,
Imran
As @mathieubossaert has provided, this will correctly calculate the desired year.
format-date(today(), '%Y%m%d')+(50-${age})
This doesnt work because format-date(today(), '%Y%m%d') returns a string, eg "20200531" (for May 31, 2020), which gets converted to the number 20200531 when used in an arithmetic axpression, to which you are adding the number 10, hence you get the number 20200541.
If you want to add 10 years to your new custom date 'number' format, in your case you'd have to add 100000. So try this:
That would be the correct expression if the service was provided today. You probably need to use the service date both to calculate the age and the components of the expiration date.
Same as above, you'll need to use the service date rather than today.
If you haven't already, I also encourage you to consider whether the form needs to compute these dates or whether it's something that can be computed in analysis.
All that said, @LN is solution is more elegant! (and in fact the one I was going to propose too as a more logical alternative, but I thought I'd fix your math first... )
I did what I imagined with external csv services list, to take advantage of pulldata function.
For each selected service I can ask the duration colmun value of the selected service and compute it with the subscription date.
)