I've been stuck on this problem for a few days now, slowly driving me crazy. I've looked all throughout this forum, but haven't found an instance of this specific issue.
My problem is pretty involved, but I've distilled it down as much as possible to the below. The example below of course isn't my use case, it's just an easily reproducible example of the core behaviour I'm trying to achieve - using nested functions and evaluating their output inside other functions.
So I have a function, say
Set f_Format = Pick(Match($1, 'num','text'), NUM($2), TEXT($2));
It takes 2 parameters. $1 to choose whether the value will be formatted as a NUM or as TEXT, and $2 is the value to be formatted.
This takes 3 parameters. $1 to decide whether to add, or subtract numbers. $2 and $3 takes two values to add or subtract.
I want to use the f_Calc function to decide what calculation to use (whether to add or subtract), return that value, then use that value in the f_Format function to format it accordingly. The issue I have is the commas in f_Calc are being used to parse f_Format, so f_Calc is never evaluated.
$(f_Format(
'num',
$(f_Calc('add',4,3))
))
I've tried Set vs Let, escaping characters, using REPLACE(), using $ expansions, I can't get anything to work.
In this way, what we are doing is to evaluate the f_Calc variable when setting it in the f_Format. Then, f_Format only needs to care about the first parameter and the result of f_Calc.
The previous way was loading all as a text, then the formula was not working because it was not understanding the pick and match parenthesis as an evaluated result and it was assigning the parameters in the wrong positions. For this reason, changing them to 5 and 6 solved partially the problem.
However, this had a flaw and it is that we needed to change 5 and 6 depending if we were adding or subtracting. With these new formulas, this issue should be avoided. Also, the measure in front end is more compact and cleaner.
Ah ok I think I understand it. The only issue with the embedding the calculation inside the format function - which is what I did initially - is I need to create one format function per calculation function. However I have several different custom calculation functions that Qlik doesnt support (for example one that does a weighted avg, one that does something else), and would like to use the same Format function for these different calculation to reduce redundancy. Hence my idea of simply using the Format function and passing it any function that returns a value (regardless of what the function does). Hope this makes sense
Try this expression. Basically, what we need to do is that f_Calc is calculated first and not together with f_Format. That is what the extra $ is doing, forcing f_Calc to become a number instead of the whole formula. Then, since the second part in the f_Format will be a number, it understands how to replace it in the expression:
I found the way to integrate them properly in the front end expression. I'm used to split the measures in different variables. For this reason, at the beginning I was modifying them. But I just found the compact expression without needing to change them.
