topic Problem with Parameterized variable in QlikView

Problem with Parameterized variable

saimahasan — Thu, 10 May 2018 08:36:18 GMT

Hi Folks,

Need your help regarding parameterized variable. I am creating a variable as

if(BO_FUND_CLOSURE_DATE>=Date($1) and BO_FUND_CLOSURE_DATE<=Date($2),Interval(BO_FUND_CLOSURE_DATE - Date($1),'d'), (Date($2) - Date($1) +1))

and when i am trying to call it in a textbox it gives me proper values but when the same is pulled into chart as an pick match expression then it gives me null values.

Below is my expression:

=Pick(Match(ValueList(vCashPlcmtLabel1,vCashPlcmntLabel2),'Current Year','Previous Year'),$(vAvgAUM(vFromDate,vToDate)),$(vAvgAUM(vPrevFromDate,vPrevToDate)))

Please help as it is acting as a showstopper.

Re: Problem with Parameterized variable

petter — Thu, 10 May 2018 08:48:18 GMT

Exactly how are you "calling" it from your textbox where it is working?

Re: Problem with Parameterized variable

saimahasan — Thu, 10 May 2018 09:52:04 GMT

In the textbox i call it with =$(vAvgAUM(vFromDate,vToDate)) sign whereas in expression within pick() it is being called as $(vAvgAUM(vFromDate,vToDate)).. May be '=' sign is creating a problem somewhere. I am not able to figure out.

Re: Problem with Parameterized variable

petter — Thu, 10 May 2018 10:33:29 GMT

Try to break it down and replace the two $-sign expansions with constants and see if it at least calculates to isolate the problem:

=Pick(Match(ValueList(vCashPlcmtLabel1,vCashPlcmntLabel2),'Current Year','Previous Year'),1,2)

Re: Problem with Parameterized variable

petter — Thu, 10 May 2018 10:39:08 GMT

What does the two variables vCashPlcmtLabel1 and vCashPlcmntLabel2 contain?

And the four variables vFromDate,vToDate,vPrevFromDate, vPrevToDate.

If they are strings shouldn't the first part of the expression be like this instead?

=Pick(Match(ValueList('$(vCashPlcmtLabel1)','$(vCashPlcmntLabel2)')........

A lot of problems stem from the fact that it is easy to forget to use aggregation functions. Henric Cronstöm is quite adamant about this:

Use Aggregation Functions!

Re: Problem with Parameterized variable

saimahasan — Thu, 10 May 2018 11:55:50 GMT

Hi,

vCashPlcmtLabel1 and vCashPlcmntLabel2 are strings but they are working fine for rest of the expressions. This problem comes only with the parameterized variable.

vFromDate is date format which shows up correctly when displayed in textbox. No idea why there is a problem with pick match function.

Re: Problem with Parameterized variable

petter — Thu, 10 May 2018 15:41:59 GMT

I guess that BO_FUND_CLOSURE_DATE is a field right?

Then you should use an aggregation function as I pointed out earlier.