Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a cyclic group of fields named A_A2 wich has the following fields:
Q8_Label
Q10_Label
Q11_Label
Now I want to avg the same fields in the expression and tried something like this formula:
=avg(Replace(getcurrentfield(A_Q2),'Label','Value'))
to get
avg(Q8_Value)
avg(Q10_Value)
avg(Q11_Value)
whenever cyclic changes.
However this doesn't work because Replace(getcurrentfield(A_Q2),'Label','Value') is always recognized as string. I tried to search for something to force it to be seen as a field but couldn't find it on the formulas or on Qlikcommunity.
Is this even possible? It seems simple enough but I can't make it work.
Any help would be appreciated. Thanks.
Kind regards,
Nuno
As you noted, the replace function returns a string but you want to calculate the average of the field (which the string represents). In this case, you want to use dollar sign expansion with evaluate. The expression will look like this:
=avg($(=Replace(getcurrentfield(A_Q2),'Label','Value')))
The $(= ... ) takes the result of the expression and places the contents of into the expression. Think of it as copying the result (the string value 'Q8_Value') and replacing the $(= ... ) with the result (the final expression evaluated is avg(Q8_Value).
As you noted, the replace function returns a string but you want to calculate the average of the field (which the string represents). In this case, you want to use dollar sign expansion with evaluate. The expression will look like this:
=avg($(=Replace(getcurrentfield(A_Q2),'Label','Value')))
The $(= ... ) takes the result of the expression and places the contents of into the expression. Think of it as copying the result (the string value 'Q8_Value') and replacing the $(= ... ) with the result (the final expression evaluated is avg(Q8_Value).
Try $(=Replace(getcurrentfield(A_Q2),'Label','Value'))
Great... it worked. Thanks both!