topic Re: Can Match use output of function in QlikView

Can Match use output of function

mjtaft2017 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community -

New to Qlikview - I needed to create intervals (< 0 ,0-30, 31-60, 61-90, 91-120, >120) based upon a date user enters in input box. So I cannot do this in load script. I created a user function to slot the intervals based on the date entered. I want to use this Match expression to sort the intervals in the order above.

Help says this is the format - so I am thinking I cannot use the output of my function as the first parm needed for Match. Is there a way to accomplish this some how? I included an example. I want the chart Opportunity Aging to have the X axis sorted by intervals in the order listed above. I thought had it working and then changed the input date and it didnt work again 😞

Match($(AgingInterval(CloseDate,vToday)),ValueList($(vInterval)))

Match ( text, mask_expr1 { ,Expression} )

Below is the function I created for the dimension - it takes 2 parameters $1 is the Date from loaded table and $2 is the date the user enters in the input box which is used to define the start date for Aging :

SET AgingInterval =

if($1 < $2,'< 0',

if(($1 >= $2 and $1 <= ($2 +30)), '0-30',

if(($1 >= ($2 +31) and $1 <= ($2 +60)), '31-60',

if(($1 >= ($2 +61) and $1 <= ($2 +90)), '61-90',

if(($1 >= ($2 +91) and $1 <= ($2 +120)), '91-120',

if(($1 >=($2 +121)), '>120 days'

))))));

Below is the variable I created for the intervals I have:

SET vIntervals = '< 0','0-30','31-60','61-90','91-120','>120 days';

Re: Can Match use output of function

sunny_talwar — Thu, 13 Jul 2017 15:28:14 GMT

May be try this

=Dual($(AgingInterval(CloseDate,vToday)), $(AgingIntervalNum(CloseDate,vToday)))

Where AgingIntervalNum is a new variable like this

if($1 < $2,0,

if(($1 >= $2 and $1 <= ($2 +30)),30,

if(($1 >= ($2 +31) and $1 <= ($2 +60)), 60,

if(($1 >= ($2 +61) and $1 <= ($2 +90)), 90,

if(($1 >= ($2 +91) and $1 <= ($2 +120)), 120,

if(($1 >=($2 +121)),150

))))))

Sort the dimension numerically now

Re: Can Match use output of function

mjtaft2017 — Thu, 13 Jul 2017 16:39:41 GMT

Thanks Sunny -

That worked great. I was not familiar with the Dual() function. I am going to experiment a little further with that.

I appreciate your help!

Mary Jo

Re: Can Match use output of function

sunny_talwar — Thu, 13 Jul 2017 16:44:58 GMT

This might work as well

SET AgingInterval =

if($1 < $2, Dual('< 0', 0),

if(($1 >= $2 and $1 <= ($2 +30)), Dual('0-30', 30),

if(($1 >= ($2 +31) and $1 <= ($2 +60)), Dual('31-60', 60),

if(($1 >= ($2 +61) and $1 <= ($2 +90)), Dual('61-90', 90),

if(($1 >= ($2 +91) and $1 <= ($2 +120)), Dual('91-120', 120),

if(($1 >=($2 +121)), Dual('>120 days', 150)

))))));

Basically, dual function assigns a numerical value to a text. First part is the text, second is the numerical value you want to assign it.

Re: Can Match use output of function

mjtaft2017 — Thu, 13 Jul 2017 17:02:26 GMT

I will try that out. I would still like to understand why I couldn't get the MATCH() function to work with my function output being the first parameter

Match($(AgingInterval(CloseDate,vToday)),ValueList($(vIntervals)))

Match ( text, mask_expr1 { ,Expression} )

I had this variable defined:

SET vIntervals = '< 0','0-30','31-60','61-90','91-120','>120 days';