Can Match use output of function

[mjtaft2017]

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';

[sunny_talwar · Accepted solution]

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

[mjtaft2017]

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

[sunny_talwar]

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.

[mjtaft2017]

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';