Problem with Parameterized variable

[saimahasan]

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.

[petter]

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

[saimahasan]

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.

[petter]

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)

[petter]

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!

[saimahasan]

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.

[petter]

I guess that BO_FUND_CLOSURE_DATE is a field right?

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