Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Evaluate formula in macro

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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!

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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 !

Not applicable
Author

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