0

I am adding a dynamic reference line to a combo widget and the line needs to show an average conversion rate.

Monthly conversion rate is presented well, as the column calculating this value is set to Custom % - so although I am seeing visually 42% etc, looking at the axis on the left, it sits between 0 and 1.

When using a binding query to bring in the average YTD conversion rate

"{{cell(ConvertedOpp_1.result, 0, \"C\").asString()}}"

I am seeing 0.42

Is there a way to change JSON so that the value itself for the dynamic reference line sits between 0 and 1, however, the visual shows 42?

Ie, is it possible to do something like this in reference line's label or value section in JSON?

"{{cell(ConvertedOpp_1.result, 0, \"C\").asString()}}" * 100

Thank you so much

enter image description here

1

In the ConvertedOpp_1 query, you could add a field with C*100 to bind it in the reference line label. Then to not have it displayed in other charts/tables remove it from the JSONs "numbers": [], "groups": [], "strings": [] section of the query.

Alternatively create a new query, where you bind "{{cell(ConvertedOpp_1.result, 0, \"C\").asString()}}" and multiply it by 100. (Perhaps attach "%") Then bind this querys result in the reference line label.

  • Brilliant, thank you so much Patrick! I've gone for the first option - it's working exactly as I wanted. Just another quick question, I can't seem to be able to get rid of the decimal points, although the column in ConvertedOpp_1 is set to be a number with no decimal points, ie in the query I see 42, and on the reference line label I see 42.1052631.... etc , thank you! – g4c Jan 8 '20 at 9:40
  • 1
    The Format is only applied to the shown output, not the underlying number. To remove the decimal places, surround the calculation with trunc( ) – Patrick R. Jan 13 '20 at 14:26
  • Awesome, thank you!! – g4c Jan 14 '20 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.