Best way to format currency in Charts

  • 1
  • Question
  • Updated 1 month ago
  • Answered
I'd like to truncate the currency totals in my charts to round to the thousands and then if its in the millions switch from K to M. 

What would be the best way to do this in Charts and Rich Text Components?
Photo of Sam Becker

Sam Becker

  • 1,158 Points 1k badge 2x thumb

Posted 2 months ago

  • 1
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,426 Points 10k badge 2x thumb
Sam,

You can do this with Model UI Formula fields.  You can format the totals and then use an IF statement to choose when to show one or the other.  Here is a page that demonstrates this:

<skuidpage personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="Opportunity">
    <models>
        <model id="OppStages" limit="20" query="true" createrowifnonefound="false" datasource="salesforce" sobject="Opportunity" type="aggregate">
            <fields/>
            <conditions/>
            <actions/>
            <groupby method="simple">
                <field id="StageName" name="stageName"/>
            </groupby>
        </model>
        <model id="Opportunity" limit="100" query="true" createrowifnonefound="false" sobject="Opportunity" datasource="salesforce">
            <fields>
                <field id="Name"/>
                <field id="CreatedDate"/>
                <field id="AccountId"/>
                <field id="Account.Name"/>
                <field id="StageName"/>
                <field id="Amount"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="" enclosevalueinquotes="true" field="StageName" state="filterableoff" inactive="true" name="StageName"/>
            </conditions>
            <actions>
                <action>
                    <actions>
                        <action type="setCondition" sourcemodel="OppStages" targetmodel="OppStages" affectedrows="context" model="OppSumAmount" condition="StageName" value="{{$Model.Opportunity.conditions.0.value}}"/>
                        <action type="requeryModel" model="OppSumAmount" behavior="standard"/>
                    </actions>
                    <events>
                        <event>models.loaded</event>
                    </events>
                </action>
            </actions>
        </model>
        <model id="OppSumAmount" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Opportunity">
            <fields>
                <field id="Amount" name="sumAmount" function="SUM"/>
                <field id="TotalWholeDollars" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" precision="9" scale="0" label="TotalWholeDollars" defaultvaluetype="fieldvalue" readonly="true" returntype="DOUBLE">
                    <formula>ROUND({{{sumAmount}}})</formula>
                </field>
                <field uionly="true" displaytype="FORMULA" id="TotalThousands" ogdisplaytype="TEXT" readonly="true" returntype="TEXT">
                    <formula>"$" + VALUE(ROUND({{sumAmount}}/1000)) + " K"</formula>
                </field>
                <field id="TotalMillions" uionly="true" displaytype="FORMULA" label="TotalMillions" ogdisplaytype="TEXT" readonly="true" returntype="TEXT">
                    <formula>"$" + VALUE(ROUND({{sumAmount}}/1000000)) + " M"</formula>
                </field>
                <field id="SwitchFormat" uionly="true" displaytype="FORMULA" label="SwitchFormat" ogdisplaytype="TEXT" readonly="true" returntype="TEXT">
                    <formula>IF({{sumAmount}}/1000000&gt;=1,{{TotalMillions}},{{TotalThousands}})</formula>
                </field>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="" enclosevalueinquotes="true" field="StageName" state="filterableoff" inactive="true" name="StageName"/>
            </conditions>
            <actions/>
            <groupby method="simple"/>
        </model>
    </models>
    <components>
        <pagetitle model="Opportunity" uniqueid="sk-3Bsoue-80">
            <maintitle>
                <template>{{Model.labelPlural}}</template>
            </maintitle>
            <subtitle>
                <template>Home</template>
            </subtitle>
            <actions>
                <action type="savecancel" uniqueid="sk-1sTJR2-232"/>
            </actions>
        </pagetitle>
        <grid uniqueid="sk-1_HxNb-443" columngutter="4px" rowgutter="2px">
            <divisions>
                <division behavior="flex" minwidth="100px" ratio="1" verticalalign="top">
                    <components>
                        <wrapper uniqueid="sk-1_pL1k-428">
                            <components>
                                <richtext multiple="false" uniqueid="sk-1iS--633" model="OppSumAmount">
                                    <contents>&lt;p&gt;&lt;span style="font-size:28px;"&gt;&lt;span style="color:#ffffff;"&gt;Opportunity Total Amount&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;

&lt;p&gt;&lt;span style="font-size:28px;"&gt;&lt;span style="color:#ffffff;"&gt;Default: {{sumAmount}}&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;

&lt;p&gt;&lt;span style="font-size:28px;"&gt;&lt;span style="color:#ffffff;"&gt;K: {{TotalThousands}}&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;

&lt;p&gt;&lt;span style="font-size:28px;"&gt;&lt;span style="color:#ffffff;"&gt;M: {{TotalMillions}}&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;

&lt;p&gt;&lt;span style="font-size:28px;"&gt;&lt;span style="color:#ffffff;"&gt;S: {{SwitchFormat}}&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;
</contents>
                                </richtext>
                            </components>
                            <styles>
                                <styleitem type="background" bgtype="color">
                                    <styles>
                                        <styleitem property="background-color" value="#2196f3"/>
                                    </styles>
                                </styleitem>
                                <styleitem type="border" borders="all" margin="all" padding="left,">
                                    <styles>
                                        <styleitem property="border" value="4px solid #2196f3"/>
                                        <styleitem property="padding-left" value="4px"/>
                                        <styleitem property="margin" value="2px"/>
                                        <styleitem property="box-sizing" value="border-box"/>
                                    </styles>
                                </styleitem>
                                <styleitem type="size" width="custom">
                                    <styles>
                                        <styleitem property="max-width" value="350px"/>
                                    </styles>
                                </styleitem>
                            </styles>
                        </wrapper>
                    </components>
                </division>
                <division behavior="flex" verticalalign="top" ratio="1" minwidth="60%">
                    <components>
                        <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="5" createrecords="true" model="Opportunity" mode="read" uniqueid="sk-3Bsoue-81" buttonposition="" hideheader="false" hidefooter="false">
                            <fields>
                                <field id="Name" allowordering="true" uniqueid="sk-1sTJRE-238"/>
                                <field id="AccountId" hideable="true" uniqueid="fi-1sTSWt-298" valuehalign="" type=""/>
                                <field id="StageName" hideable="true" uniqueid="fi-1_HXV1-265" valuehalign="" type=""/>
                                <field id="CreatedDate" allowordering="true" uniqueid="sk-1sTJRG-241"/>
                                <field id="Amount" hideable="true" uniqueid="fi-9SXP_-327">
                                    <summaries>
                                        <summary>sum</summary>
                                    </summaries>
                                </field>
                            </fields>
                            <rowactions>
                                <action type="edit"/>
                                <action type="delete"/>
                            </rowactions>
                            <massactions usefirstitemasdefault="true">
                                <action type="massupdate"/>
                                <action type="massdelete"/>
                            </massactions>
                            <views>
                                <view type="standard"/>
                            </views>
                            <filters>
                                <filter type="select" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="manual" filtermethod="server" labelmode="no" condition="StageName" filteroffoptionlabel="Any Stage">
                                    <sources>
                                        <source type="model" effectsbehavior="justdefault" model="OppStages">
                                            <labeltemplate>{{{stageName}}}</labeltemplate>
                                            <valuetemplate>{{{stageName}}}</valuetemplate>
                                        </source>
                                    </sources>
                                </filter>
                            </filters>
                            <searchfields/>
                        </skootable>
                    </components>
                </division>
            </divisions>
            <styles>
                <styleitem type="background" bgtype="none"/>
            </styles>
        </grid>
    </components>
    <resources>
        <labels/>
        <css/>
        <javascript>
            <jsitem location="inline" name="newInlineJS" cachelocation="false" url="">(function(skuid){
    var $ = skuid.$;
    $(document.body).one('pageload',function(){
    var tablecomponent = skuid.$C('sk-3Bsoue-81');
   
    console.log(tablecomponent);
    });
})(skuid);</jsitem>
        </javascript>
        <actionsequences uniqueid="sk-1sTSWe-293"/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>


Thanks,

Bill


(Edited)
Photo of Sam Becker

Sam Becker

  • 1,158 Points 1k badge 2x thumb
Thanks for the reply Bill! I was hoping there would be an easier way that I just couldn't figure out. But, I'll just have to do this.

Appreciate it!