Custom Functions

[BIAKS]

Hello,

I created a custom function to tidy up my script. The below code repeats itself 6 times, I can make it work with parameterized variables like below:

Set vFark=
Interval(
	rangesum(
		NetWorkDays(Peek($1)+1, $1-1, $(vResmiTatiller), $($2)) * ($(vMesaiSaati)/24)	// 9h per workday, for all days inbetween the period, excluding bounderies 
		,if(NetWorkDays($1, $1, $(vResmiTatiller), $($2)), frac($1),0)  														// working hours last day
		,if(NetWorkDays(Peek($1),Peek($1), $(vResmiTatiller), $($2)),($(vMesaiSaati)/24)-frac(Peek($1)),0) 				// working hours first day
		,if(NetWorkDays(Peek($1),Peek($1), $(vResmiTatiller), $($2)) and floor(Peek($1))=floor($1),-1*($(vMesaiSaati)/24)) 	// correct for first equals last day
	)
)
;

and used that vFark in the load as below:

$(vFark(FIRSTRESPONSE, vVacYK))

FIRSTRESPONSE is a date field, vVacYK is a variable that has these values: '14.01.2026','15.01.2026' and vResmiTatiller has all holiday dates in the same format with vVacYK:

The problem is, if I dont make it a custom function it works and show 0 as intended, but in custom function it returns values higher than 0 for the vacation days.
Can you please help me?

Thank you.

[marcus_sommer]

It looks that your comma-separated dates-list is causing your issues because the comma will be treated as parameter-delimiter from the variable.

There exists no way in Qlik to mask them - therefore you need either a different function/syntax which worked without such comma-list or a replace-approach for the critical char (forward + backward) or transferring the information with n separate parameters.

At the moment I have no ideas how to switch the function/syntax (applying similar logic in the UI by set statements there are various ways to define them) and applying n parameters seems not really sensible. But a replace of the comma with any other char like a pipe and then replacing it within another $-sign expansion in the function may be a practically workaround.

Beside this you may consider to change the logic completely, for example by defining your working-days within the calendar. It's there a simple two step approach of adding a 0/1 flag if a day is a working-day or not (querying the weekends per weekday() and mapping the holidays/special-days from an external table) and the next step is accumulating this flag within another field. The networkdays are then just the difference from the date 1/2 mapping against the calendar(extract). Nearly all kind of date/offset-calculation could be with such logic replaced or at least significantly simplified.

[BIAKS]

Hi Marcus,

Thanks for your reply.

I didn't quite understand what you meant but the below formula works with my date variables:

Interval(
					rangesum(
						NetWorkDays(Peek(FIRSTRESPONSE)+1, FIRSTRESPONSE-1, $(vResmiTatiller), $(vVacYK)) * ($(vMesaiSaati)/24)															
						,if(NetWorkDays(FIRSTRESPONSE, FIRSTRESPONSE, $(vResmiTatiller), $(vVacYK)), frac(FIRSTRESPONSE),0)  															
						,if(NetWorkDays(Peek(FIRSTRESPONSE),Peek(FIRSTRESPONSE), $(vResmiTatiller), $(vVacYK)),(1*($(vMesaiSaati)/24))-frac(Peek(FIRSTRESPONSE)),0) 						
						,if(NetWorkDays(Peek(FIRSTRESPONSE),Peek(FIRSTRESPONSE), $(vResmiTatiller), $(vVacYK)) and floor(Peek(FIRSTRESPONSE))=floor(FIRSTRESPONSE),-1*($(vMesaiSaati)/24))
					)
				), 

What is the difference between the code above and the one in vFark variable? Also it is calculating the networkdays but giving the wrong results.

[marcus_sommer]

This statement from the second if-loop:

(1*($(vMesaiSaati)/24))-frac(Peek(FIRSTRESPONSE)),0)

is different to:

($(vMesaiSaati)/24)-frac(Peek($1)),0)

Beside this - don't add comments within the variables because it has potential to get in interaction with the wanted script and could make a troubleshooting quite expensive.

[BIAKS]

That is not a difference in algorithm which I meant. They are almost identical but one is working and the other is not. I want to know the reason behind this so I can make it work or learn that there is no way to do it this way.

[marcus_sommer]

My approach to build multi-step logic respectively to troubleshoot unexpected results is to distribute it into n separate and parallel steps to check if they are working on this level respectively to adjust them. In the worst-case to the most granular level but usually starting with the half and then the half from the half ... Mostly it's not a detour else a fast method because looking for mistakes in more complex logic could become rather painfully ...

This is my general way with native script - the transfer of the sub-parts into n variables and then combining and/or nesting them comes afterwards. If then anything is failing the cause is the creation and/or calling the variables. And here are starting special difficulties because depending on the kind of content of the variables - numbers, strings, with/without included commas/brackets/quotes - the creation and calling might be different. Therefore the usage of combined and/or nested variables should be considered carefully because the complexity could become easily higher as the aim of variables to simplify an approach.

A special challenge is the use of comments within the variables - because each one checked alone may working like expected and the combined/nested it doesn't work anymore. The reason is that the entire variable including the comments are at first combined/nested and then starts their evaluation and here the comments could touch the other variables, like:

set v1 = 1 // it's aimed as ...;
set v2 = 2;
let v3 = $(1) + $(2);

what happens then is:

let v3 =  1 // it's aimed as ... + 2;

Therefore my previous suggestion to add no comments within the variables to avoid any potentially struggle.

Beside all this be aware that you are using peek() and field-references within the variables which are depending on various factors, for example needs peek() a defined sorting within a resident-load and field-references might be easily failing by (the hot loved) usage of qualify-statements. This means unexpected results might be also caused from a different underlying data-set. 

[BIAKS]

Ok, I tried to make a simple scenario with same use case:

Calendar:

NoConcatenate

Load

Date(IterNo()+StartDate-1) as CalendarDate

While IterNo()+StartDate-1 <= EndDate;

Load * Inline [

StartDate, EndDate

01.01.2026, 30.01.2026

];

Vacations:

NoConcatenate

Load

Date(IterNo()+StartDate-1) as VacationDates

While IterNo()+StartDate-1 <= EndDate;

Load * Inline [

StartDate, EndDate

03.01.2026, 10.01.2026

];

VacationConcat:

NoConcatenate

Load

Concat(chr(39) & VacationDates & chr(39),',' ) as Vacations

Resident Vacations;

Drop Table Vacations;

Let vVacations = FieldValue('Vacations',1);

Set vNetWork = NetWorkDays($1, $1, $(vVacations));

NetDays:

NoConcatenate

Load

CalendarDate,

// NetWorkDays(CalendarDate, CalendarDate, $(vVacations)) as IsWorkingDay

$(vNetWork(CalendarDate)) as IsWorkingDay

Resident Calendar;

Drop Table Calendar;

If I change 

$(vNetWork(CalendarDate)) as IsWorkingDay 

to 

$(vNetWork(CalendarDate, vVacations)) as IsWorkingDay 

and 

Set vNetWork = NetWorkDays($1, $1, $(vVacations));

to

Set vNetWork = NetWorkDays($1, $1, $($2));

it doesn't work. But I think it should work.

[marcus_sommer]

In general could such approach be working. If it doesn't it means that there are any issues with the syntax or the data. In this example are both dates equally $1 and it might be that the call of the date may look like '$1' and that $($2) expands the date-list and causing the comma-problem ...

Like hinted the combination/nesting of variables isn't trivial and should be no standard-approach - else only be used in a few special scenarios which are be worth to invest extra efforts. In my early days it did a lot of such stuff but the development-times are too long and even much more important the maintaining becomes easily a nightmare ... especially if anything goes wrong and it needs a fast fix.

Like mentioned above I wouldn't use networkdays() because there are better and more powerful methods. It goes in this direction:

Vacations:

mapping Load

Date(IterNo()+StartDate-1) as Lookup, 1 as Return

While IterNo()+StartDate-1 <= EndDate;

Load * Inline [

StartDate, EndDate

03.01.2026, 10.01.2026

];

Calendar:

load *, rangesum(peek('FlagCum'), Flag) as FlagCum;
load *, if(weekday(CalendarDate) >= 5 or applymap('Vacations', CalendarDate, 0), 0, 1) as Flag;

Load

Date(IterNo()+StartDate-1) as CalendarDate

While IterNo()+StartDate-1 <= EndDate;

Load * Inline [

StartDate, EndDate

01.01.2026, 30.01.2026

];

If any loads require a networkday-calculation the date + the accumulated value are extracted within another mapping and then:

applymap('CalendarWorkDays', Date1, null()) - applymap('CalendarWorkDays', Date2, null()) 

returns the workdays.

Within the UI it could be used similar with a:

max(Date) - min(Date)

or quite often just with:

sum(Flag)

[BIAKS]

Thank you for your explanation, but I currently don't have any problem with networkdays.

I used the same function a lot and I wanted to reduce it so I tried custom functions. Like you said, maybe Qlik doesn't handle variables like dimensions so I can't apply my logic. I wish there was a better debug mode for this so I could check the evaluations behind the script. Standart debug doesn't go deep into variable evals or any load step. 

Thank you for your time and effort, again. I will continue with my repeated function 😕