CALC - AppDaddy-Software-Solutions-Inc/framework-markup-language GitHub Wiki

<CALC/> : Transform

The <CALC/> transform is used to calculate summary information in a data source.

<CALC/>'s are often used to calculate row totals, min or max values etc. It is also useful when used in combination with other data transforms like <FILTER/>.

Attributes

Name Type Default Description Req
operation string The operation to perform.
  • sum - calculates the sum of non-null source field value's within the group (if defined)
  • avg, average - calculates the average of non-null source field value's within the group (if defined)
  • min - calculates the minimum of non-null source field value's within the group (if defined)
  • max - calculates the maximum of non-null source field value's within the group (if defined)
  • count, cnt - calculates the total number of non-null occurrences of the specified source fields within the group (if defined)
  • total - calculates the total number of unique values within the specified source field within the group (if defined)
    		•  ✔
    source string The name of the field to use in the operation ✔
    target string The name of the field to store the result of the operation,

    Be careful to specify a name that is unique since calculations will overwrite fields by the same name.    		 ✔
    precision int If the result of the operation is numeric, precision can be used to round the result to the specified number of digits
    group string A ; separated list of one or more field names to use for grouping within the operation

    Examples

    logo See it in Action

    For in-depth examples and full applications, visit fml.dev!

    Examples

    All examples will use this example data from this endpoint: api.web/cities/nearby

    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
	</ROW>
</DATA>
    (fig. 1)

    sum

    <DATA id="DB1" autoexecute="false">
	<URL><![CDATA[api.web/cities/nearby]]></URL>
	<CALC target="TOTAL" source="POPULATION" operation="sum"/>
</DATA>
    Show fig. 1 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<TOTAL>5700000</TOTAL>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<TOTAL>5700000</TOTAL>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<TOTAL>5700000</TOTAL>
	</ROW>
</DATA>
    (fig. 2)

    avg

    <DATA id="DB1" autoexecute="false">
	<URL><![CDATA[api.web/cities/nearby]]></URL>
	<CALC target="AVERAGEPOP" source="POPULATION" operation="avg"/>
</DATA>
    Show fig. 2 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<AVERAGEPOP>1900000</AVERAGEPOP>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<AVERAGEPOP>1900000</AVERAGEPOP>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<AVERAGEPOP>1900000</AVERAGEPOP>
	</ROW>
</DATA>
    (fig. 3)

    min

    <DATA id="DB1" autoexecute="false">
	<URL><![CDATA[api.web/cities/nearby]]></URL>
	<CALC target="LOWESTPOP" source="POPULATION" operation="min"/>
</DATA>
    Show fig. 3 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<LOWESTPOP>990000</LOWESTPOP>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<LOWESTPOP>990000</LOWESTPOP>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<LOWESTPOP>990000</LOWESTPOP>
	</ROW>
</DATA>
    (fig. 4)

    max

    <DATA id="DB1" autoexecute="false">
	<URL><![CDATA[api.web/cities/nearby]]></URL>
	<CALC target="HIGHESTPOP" source="POPULATION" operation="max" precision="-6"/>
</DATA>

    *note the -6 precision rounds like so 2930000 -> 2.930000 -> 3 -> 3000000

    Show fig. 4 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<HIGHESTPOP>3000000</HIGHESTPOP>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<HIGHESTPOP>3000000</HIGHESTPOP>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<HIGHESTPOP>3000000</HIGHESTPOP>
	</ROW>
</DATA>
    (fig. 5)

    Everyone datarow that contains a field will be counted count

    <DATAid="DB1" autoexecute="false">
	<URL><![CDATA[api.web/cities/nearby]]></URL>
	<CALC target="ROWCOUNT" source="POPULATION" operation="count"/>
</DATA>
    Show fig. 5 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<ROWCOUNT>3</ROWCOUNT>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<ROWCOUNT>3</ROWCOUNT>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<ROWCOUNT>3</ROWCOUNT>
	</ROW>
</DATA>
    (fig. 6)

    eval

    <DATA id="fig6" autoexecute="false">
	<URL><![CDATA[api.web/cities/nearby]]></URL>
	<CALC target="GROWTH_20PERCENT" source="{POPULATION} * 1.2" operation="EVAL" />
</DATA>
    Show fig. 6 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>17800000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<GROWTH_20PERCENT>2136000</GROWTH_20PERCENT>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<GROWTH_20PERCENT>1188000</GROWTH_20PERCENT>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<GROWTH_20PERCENT>3516000</GROWTH_20PERCENT>
	</ROW>
</DATA>
    (fig. 7)

    total Using the total, we get an added field to each row to count the unique occurrences of each. In this case we will use COUNTRY and add a target field COUNTRYCOUNT:

    <DATA id="fig7" autoexecute="false">
	<CALC target="COUNTRYCOUNT" source="{data.COUNTRY}" operation="total"/>
</DATA>
    Show fig. 7 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>1780000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
                <COUNTRYCOUNT>2</COUNTRYCOUNT>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
                <COUNTRYCOUNT>2</COUNTRYCOUNT>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Italy</COUNTRY>
                <COUNTRYCOUNT>1</COUNTRYCOUNT>
	</ROW>
</DATA>
    (fig. 8)

    Using Datasource id="fig6" fig. 6 as the starting point for Datasource id="fig7", we will expand upon those datarows using sequenced operations. Combining sequences of operations allows you to use previous CALCs within a later operation as shown below.

    <DATA id="fig8" value="{fig6.data}" autoexecute="false">
	<CALC target="GROWTH_20PERCENT_W_INFLUX" source="{GROWTH_20PERCENT} + 135500" operation="EVAL" />
	<CALC target="GROWTH_PERCENTAGE_INC_INFLUX" source="(({GROWTH_20PERCENT_W_INFLUX} / {data.POPULATION}) - 1) * 100" operation="EVAL" precision="2" />
	<CALC target="AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX" source="GROWTH_PERCENTAGE_INC_INFLUX" operation="avg" precision="0"/>
</DATA>
    Show fig. 8 `Datasource` 
    <DATA>
	<ROW>
		<POPULATION>1780000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Montreal</CITY>
		<GROWTH_20PERCENT>2136000</GROWTH_20PERCENT>
		<GROWTH_20PERCENT_W_INFLUX>2271500</GROWTH_20PERCENT_W_INFLUX>
		<GROWTH_PERCENTAGE_INC_INFLUX>27.61</GROWTH_PERCENTAGE_INC_INFLUX>
		<AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX>28</AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX>
	</ROW>
	<ROW>
		<POPULATION>990000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Ottawa</CITY>
		<GROWTH_20PERCENT>1188000</GROWTH_20PERCENT>
		<GROWTH_20PERCENT_W_INFLUX>1323500</GROWTH_20PERCENT_W_INFLUX>
		<GROWTH_PERCENTAGE_INC_INFLUX>33.67</GROWTH_PERCENTAGE_INC_INFLUX>
		<AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX>28</AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX>
	</ROW>
	<ROW>
		<POPULATION>2930000</POPULATION>
		<COUNTRY>Canada</COUNTRY>
		<CITY>Toronto</CITY>
		<GROWTH_20PERCENT>3516000</GROWTH_20PERCENT>
		<GROWTH_20PERCENT_W_INFLUX>3651500</GROWTH_20PERCENT_W_INFLUX>
		<GROWTH_PERCENTAGE_INC_INFLUX>24.62</GROWTH_PERCENTAGE_INC_INFLUX>
		<AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX>28</AVERAGE_PERCENTAGE_GROWTH_INC_INFLUX>
	</ROW>
</DATA>

    Other Widgets You May Find Useful

    Framework Markup Language is an open source programming language created by AppDaddy Software Solutions Inc. FML and is licensed under a fair source license agreement and is maintained by a community of developers.

    ⚠️ **GitHub.com Fallback** ⚠️