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

[Quentinn]

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

[Quentinn]

bump, anyone?

[marcus_sommer]

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

[Quentinn]

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