function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
PetyaPetya 

Google Chart with formula

Hello 

 

I made a formula using a google chart link.

IMAGE("http://chart.googleapis.com/chart?chf=bg,s,DDF8CC&chxl=0:|0|20.000|40.000|60.000|80.000|1.000.000|1:|Margin+2010|Margin+2011|Margin+2012&chxr=0,0,160|1,5,100&chxs=0,676767,11.5,0,lt,676767&chxt=x,y&chbh=a,4,4&chs=450x150&cht=bhs&chco=008000,FF0000&chds=5,1000000,5,1000000&chd=t:"
+ SUBSTITUTE(TEXT(Margin_2012__c), ".","")+"," 
+ SUBSTITUTE(TEXT(Margin_2011__c), ".","")+"," 
+ SUBSTITUTE(TEXT(Margin_2010__c), ".","") 
+"|1000000,1000000,1000000", "No Image available", 150, 450)

 

This is a chart, which shows the margin by year in comparison with the target. The last line shows the target for 3 years, always 1 Mio. I would like to add a field, to allow the user to define the annual target. How could I add the value from this additional field to my formula?

Now the user needs to change the target directly in the formula, for example when the target should be more than 1 Mio, the user must go to the formula and add the value to the last line. It could be simpler when he changes only the appropriate field on the UI and the value in the chart changes automatically.

 

When I change the last line to +"|My_Target1_field__c,My_Target2_field__c,My_Target3_field__c", "No Image available", 150, 450) I receive an empty chart and I have date in my target fields.

 

Any suggestions?

Ashish_SFDCAshish_SFDC

Hi Petya,

 

I did not clearly understand the significance of the numbers - +"|1000000,1000000,1000000",  in the last line?

If you wish to replace the numbers with fields – then those fields must be properly Bounded to the related records.

As the fields - Margin_2012__c are wrapped in a () Braces i guess it should be +"|(My_Target1_field__c),(My_Target2_field__c),(My_Target3_field__c)", "No Image available", 150, 450)

Not sure if that works, but you may try. 

 
PetyaPetya

Hi and thank you for your answer.

The last line "|1000000,1000000,1000000", shows the background rows in my chart, which show the defined target per year. For example here I defined the target for the 3 years as 1.000.000 and that’s way I have the line  "|1000000,1000000,1000000"

I already tried this option, but it doesn’t work. When I use the fields in my formula on this way, I don’t get any results in my chart, and I have values for my target-fields.
Ashish_SFDCAshish_SFDC

Hi Petya, 

 

Merge fields should not be in "Doubel Quotes", check if this one works - 

"|"+My_Target1_field__c+","+My_Target2_field__c+","+My_Target3_field__c

 
PetyaPetya

Hi and thank you

 

it works on this way, but only partially, because to see the changed axis I need also to change this parameter in my formula chds=5,1000000,5,1000000. The parameter shows the number of values on my axis and the largest value, and here the largest value is still 1 Mio

 

IMAGE("http://chart.googleapis.com/chart?chf=bg,s,DDF8CC&chxl=0:|0|20.000|40.000|60.000|80.000|1.000.000|1:|Margin_2011|Margin_Margin+2012|Margin_Margin+2013&chxr=0,0,160|1,5,100&chxs=0,676767,11.5,0,lt,676767&chxt=x,y&chbh=a,4,4&chs=450x150&cht=bhs&chco=008000,A42C2C&chds=5,1000000,5,1000000&chd=t:"
+ SUBSTITUTE(TEXT(Margin_2013__c ), ".","")+"," 
+ SUBSTITUTE(TEXT(Margin_2012__c), ".","")+"," 
+ SUBSTITUTE(TEXT(Margin_2011__c), ".","")+"|" 
+ SUBSTITUTE(TEXT(Target1__c), ".","")+"," 
+ SUBSTITUTE(TEXT(Target2__c), ".","")+"," 
+ SUBSTITUTE(TEXT(Target3__c), ".",""), "No Image available", 150, 450)