topic Re: Evaluate formula in macro in QlikView

Evaluate formula in macro

Fri, 31 May 2013 11:16:14 GMT

Hi all,

I have got this kind of formula in a lot of textboxes. To avoid copy mistakes and maybe do a quick change on the formula in every box, I want to evaluate the value by a macro and just give parameters to it. Is this possible?

My formula:

only({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},rowid={`$(=min({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},KPI_Category_Name={"$(=FieldValue('KPI_Category_Name',1))"}>}rowid,1))`}>} Statuscolor)

Can someone tell me how to evaluate this in a macro? I never implemented a macro but tried it with

sub value()
    value = activedocument.Evaluate("formula");
end sub

but the " " dont work because there are also some inside the formula.

Thanks for any kind of help!

Kind regards

Re: Evaluate formula in macro

Gysbert_Wassenaar — Fri, 31 May 2013 11:22:15 GMT

Try square brackets instead of the double quotes:

only({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},rowid={`$(=min({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},KPI_Category_Name={[$(=FieldValue('KPI_Category_Name',1))]}>}rowid,1))`}>} Statuscolor)

Re: Evaluate formula in macro

Fri, 31 May 2013 11:47:21 GMT

Thank you, this totally works fine with a macro giving an msgbox.

What I expected to do is :

function value_ret()
    value_ret =  activedocument.Evaluate("only({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},rowid={`$(=min({<KPI_Year={'$(vYearMaxU)'},KPI_Month={'$(vMonthMaxU)'},KPI_Category_Name={[$(=FieldValue('KPI_Category_Name',1))]}>}rowid,1))`}>} Statuscolor)")
end function

and then say in a textbox

=value_ret()

to set the value in the textbox (later on with variables). Is this even possible because I dont get any values.

Thank you!

Re: Evaluate formula in macro

Gysbert_Wassenaar — Fri, 31 May 2013 11:51:34 GMT

Not with a macro. But you can simply use a variable instead. Put the expression in a variable and put $1, $2 etc in the expression as placeholders for any parameters you want to pass.

Re: Evaluate formula in macro

Fri, 31 May 2013 12:01:24 GMT

and if I call the variable in a textbox with =$(myvariable) ... how can I pass the real value for the placeholders?

thank you got it !

Re: Evaluate formula in macro

Fri, 31 May 2013 12:34:13 GMT

Wow, this is damn powerful, I feel like I knew nothing. Thanks a lot!