topic Re: Need help substituting a list of holidays with a variable in a formula in App Development

Need help substituting a list of holidays with a variable in a formula

Quentinn — Wed, 28 Dec 2022 15:28:31 GMT

Hello everyone,

With some research I made a formula for calculating a turnaround time, excluding holidays and weekend, except for when the endtime (firstvalidating) falls on a weekend or holiday, then the hours of those days are included.
I made a variable "feestdagen_concat" which is a concantenated list of holidays
When substitute the list of holidays ('xx/xx/xxx', 'xx/xx/xxxx',...) with this variable in the formula the trunaround time isn't calculated correctly anymore, when I take the variable (thus the concatenated list) and put this list in the formula it works fine.
I tried with a "$" etc but I can't get it to work

What am I doing wrong?

= interval(NetWorkDays(RegistrationDateTime, if(weekday(FirstValidatingDateTime) = 0 , if (match (Floor(FirstValidatingDateTime + 1), Feestdagen_Concat), Floor(FirstValidatingDateTime + 2), Floor(FirstValidatingDateTime + 1)),
if(weekday(FirstValidatingDateTime) = 6, Floor(FirstValidatingDateTime + 2), FirstValidatingDateTime)), Feestdagen_Concat) -2
+ (1-frac(RegistrationDateTime)) + frac(if(weekday(FirstValidatingDateTime) = 6 or weekday(FirstValidatingDateTime) = 0 , Floor(FirstValidatingDateTime), FirstValidatingDateTime)))

Re: Need help substituting a list of holidays with a variable in a formula

Quentinn — Thu, 29 Dec 2022 09:37:10 GMT

bump, anyone?

Re: Need help substituting a list of holidays with a variable in a formula

marcus_sommer — Mon, 02 Jan 2023 14:17:11 GMT

The most likely explanation is that your variable doesn't contained the right values within the right syntax. I suggest to simplify your variable and expression for the testing - no concat() just two manual values in the variable and no if-loops just the networkday() and then looking how the variable-value is resolved. Within QlikView this could be observed within an empty label for an expression - in Sense I'm not sure, I believe there is a resolution anywhere within the expression-window.

- Marcus

Re: Need help substituting a list of holidays with a variable in a formula

Quentinn — Mon, 02 Jan 2023 14:47:16 GMT

Hi Marcus,

Yes that’s what I thought. I found out my format is DD/MM/YYYY while Qlik uses the standard format MM/DD/YYYY. Unfortunately this doesn’t change anything.

I’ll try a simplified syntax to test