topic Re: Custom Functions in App Development

Custom Functions

BIAKS — Tue, 10 Mar 2026 14:29:54 GMT

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.

Re: Custom Functions

marcus_sommer — Wed, 11 Mar 2026 12:38:35 GMT

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.

Re: Custom Functions

BIAKS — Wed, 11 Mar 2026 12:55:40 GMT

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.

Re: Custom Functions

marcus_sommer — Wed, 11 Mar 2026 14:27:53 GMT

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.

Re: Custom Functions

BIAKS — Thu, 12 Mar 2026 06:03:35 GMT

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.

Re: Custom Functions

marcus_sommer — Thu, 12 Mar 2026 06:54:12 GMT

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.

Re: Custom Functions

BIAKS — Thu, 12 Mar 2026 07:36:30 GMT

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

$(vNetWork(CalendarDate, vVacations)) as IsWorkingDay

and

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

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

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

Re: Custom Functions

marcus_sommer — Thu, 12 Mar 2026 14:00:30 GMT

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)

Re: Custom Functions

BIAKS — Thu, 12 Mar 2026 14:21:34 GMT

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 😕