Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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';
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
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
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
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.
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';