Free cookie consent management tool by TermsFeed

Sales by Country and Currency

This pattern illustrates how to create two different charts. One chart shows sales (calculated in US dollars) by country and the currency paid. The other shows sales by currency type, comparing the number of sales paid in US dollars versus the local currency. This pattern also provides a sample scenario to show how you can take common business requirements and quickly turn them into a report.

You'll notice that this pattern provides more than just an expression, it shows you the fastest way to build reports in Design Mode. To get the most out of this pattern, follow the steps in Create this pattern to learn how to build advanced reports using the latest low-code features.

Scenario

Account managers at the Appian Retail company want to know how much of their year-to-date sales are generated by country and currency type. Although all sales are calculated in US dollars, some countries accept local currency as payment. For example, in Germany, sales occur in both US dollars and Euros, while sales in the United Kingdom only occur in pounds.

To better understand their customer demographic, account managers want to see two different charts:

  • One chart that shows sales (calculated in US dollars) for each country and the currency used for payment.
  • Another chart that shows sales by currency type, comparing sales numbers in US dollars versus the local currency.

The report will look like this:

images/salesByCountryAndCurrency.png

Setup

This pattern uses data from the Appian Retail application, available for free in Appian Community Edition. To follow along with this pattern, log in to Appian Community to request the latest Appian Community Edition site.

If you do not see the Appian Retail application available in your existing Appian Community Edition, you can request a new Appian Community Edition to get the latest application contents available.

This pattern will use data from the following record types in the Appian Retail application:

  • Order record type: Contains order information like the order number, date, status, and whether it was purchased online or in stores. For example, order number SO43659 was purchased in store on 5/31/2019 and the order is closed.
  • Country record type: Contains country names. For example, the United States or Australia.
  • Currency Rate record type: Contains the average and end-of-day exchange rates between two currencies. For example, the average exchange rate from the US dollar to the Canadian dollar is 1.46, and the end-of-day rate is 1.47.
  • Currency record type: Contains the names of the various currencies used in sales. For example, Euro, United Kingdom pound, and Canadian dollar.

Tip:  In a rush? No worries, check out the AR_SalesByCountryAndCurrency interface available in the Appian Retail application to see the final result of this pattern.

Create this pattern

This pattern contains two different charts. We'll break down the steps to create both in the following sections:

  1. Create and set up a new interface.
  2. Create the Sales by Country in US Dollars column chart.
  3. Create the Sales by Currency Type bar chart.

Step 1: Set up the interface

We’ll start this pattern by creating and setting up our interface. Since we’ll display two different charts in our report, we’ll use a Columns Layout to separate the two components.

To create and set up the interface:

  1. In the Appian Retail application, go to the Build view.
  2. Click NEW > Interface.

  3. Configure the following properties:

    Property Value
    Name Enter AR_SalesByCountryAndCurrencyPattern.
    Save In Select the Rules & Constants folder.
  4. Click CREATE.
  5. From the PALETTE, drag a COLUMNS component into the interface.
  6. Click next to one of the Column Layouts to delete a column. We only need two columns for this pattern.

The interface currently looks like this:

images/two-empty-columns.png

Step 2: Create the Sales by Country in US Dollars column chart

Now we’ll start building our first chart.

The Sales by Country in US Dollars chart will display the year-to-date sales (calculated in U.S. dollars) generated by each country and the currency those sales were initially made in.

To display this information, we'll use a column chart with two different groupings: the first grouping will categorize all sales by country name, while the second grouping will categorize the sales in those countries by currency type.

The final chart will look like this:

images/sales-by-country-chart3.png

Create a column chart

First, we'll add a column chart to the interface and define our primary grouping and measure.

To create the column chart:

  1. From the PALETTE, drag a COLUMN CHART component into the left Column Layout.

  2. From Data Source, select RECORD TYPE and search for the Order record type.

    Tip:  Why are we using the Order record type?

    When choosing the source of your chart, you want to select a record type that can easily reference all your related data. Since we will be using data from the Currency, Currency Rate, and Sales Region record types, the Order record type is our best starting point.

    images/which-relationship-to-use.png

  3. For Primary Grouping:
    • Remove the existing field selection.
    • Use the dropdown to hover over the salesRegion > country relationship and select the name field. The field will display as salesRegion.country.name.
  4. For Measure:
    • Change the aggregation function from Count of to Sum of.
    • Remove the existing field selection.
    • Use the dropdown to select the totalDue field.
  5. Click FILTER RECORDS. We'll use a filter to only display year-to-date sales.
  6. Click Add Filter and configure the following conditions:
    • For Field, select orderDate.
    • For Condition, select Date Range.
    • For Value, select Year-to-Date.
  7. Click OK.
  8. For Sort, click ADD SORT.
  9. For Sort By:
    • Leave the default selection Alias.
    • Use the dropdown to select the totalDue_sum_measure1 alias.
    • Leave the default order of Descending.
  10. For Label, enter Sales by Country in US Dollars (YTD).

The chart currently looks like this:

images/sales-by-country-chart1.png

Add a secondary grouping

Next, we'll add a secondary grouping to our chart so we can see the different currencies used for payment in each country.

To display this information, we'll use the name field from the Currency record type.

To add a secondary grouping:

  1. For Secondary Grouping, click ADD GROUPING.

  2. Use the dropdown to hover over the currencyRate > toCurrency relationship and select the name field. The field will display as currencyRate.toCurrency.name.

    Tip:  I see a toCurrency and a fromCurrency relationship. What's the difference between these two?

    Both the toCurrency and fromCurrency relationships establish a relationship between the Currency Rate and Currency record types; however, they have different common fields in order to return different data. The toCurrency relationship uses the toCurrencyCode field as the common field to convert dollars to the local currency. The fromCurrency relationship uses the fromCurrencyCode field as the common field to convert the local currency into dollars.

  3. For Stacking, select Normal.

The chart currently looks like this:

images/sales-by-country-chart2.png

Notice that there is a secondary grouping labeled [Series 1]. This means that there is a missing currency name for one of the values in our chart.

The reason for this is that US dollars is not listed as a currency name. Since we are sourcing data from the toCurrency relationship, the chart is only getting values for local currencies that will be converted into US dollars, so US dollars is excluded from this list.

Since we want US Dollars to appear as a secondary grouping in our chart, we will create a new custom record field that replaces any any null currencies associated with an order with "US Dollars". Then, we'll use that new field as our secondary grouping.

Create a custom record field to replace null values

We'll create our new custom record field on the Order record type.

We'll create a custom record field that evaluates in real-time so we can reference related data from the Currency record type.

Custom record fields that evaluate in real-time leverage a special set of functions called Custom Field functions. For this pattern, we'll use the Custom Field function called a!customFieldDefaultValue(), which allows us to replace null values with record fields, related record fields, or literal values.

To create the custom record field:

  1. In the Appian Retail application, open the Order record type.
  2. Click NEW CUSTOM RECORD FIELD.
  3. From SELECT A TEMPLATE, choose Write Your Own Expression.
  4. In the right-hand pane, select Real-time evaluation.
  5. Click NEXT.

  6. Copy and paste the following expression in the Expression dialog:

    1
2
3
4

      a!customFieldDefaultValue(
     value: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate.relationships.{49d04656-5c49-4793-beab-acff7bf3ff9b}toCurrency.fields.{3355f795-4a22-413e-a1bc-5d22269e4ae4}name',
     default: "US Dollars"
     )
  7. Click TEST to preview the results.
  8. Click NEXT.
  9. For Name, enter currency.
  10. Click CREATE. The new field currency appears in the list of fields available on the Order record type.
  11. Click SAVE CHANGES.

Add the custom record field to the chart

Now that we have our custom record field, let’s add it to the chart so the [Series 1] label is replaced with US Dollars.

To add the custom record field to the chart:

  1. Return to the AR_SalesByCountryAndCurrencyPattern interface.
  2. For Secondary Grouping:
    • Remove the existing field selection.
    • Use the dropdown to select the currency field.
  3. Click SAVE CHANGES.

The final chart looks like this:

images/sales-by-country-chart3.png

Step 3: Create the Sales by Currency Type bar chart

Next we'll create our second chart.

The Sales by Currency Type chart will display year-to-date sales by currency type, comparing sales numbers in US dollars versus the local currency.

To create this chart, we’ll group by currency name to see all local currencies. Then, we’ll add two measures to our chart: one calculating the total sales in US dollars, and another calculating the total sales in the local currency.

The final chart will look like this:

images/sales-by-currency3.png

Create a bar chart

We’ll start by adding a bar chart component to our interface and defining a primary grouping and measure.

To create the bar chart:

  1. From the PALETTE, drag a BAR CHART component into the right Column Layout.
  2. From Data Source, select RECORD TYPE and search for the Order record type.
  3. For Primary Grouping:
    • Remove the existing field selection.
    • Use the dropdown to hover over the currencyRate > toCurrency relationship and select the name field. The field will display as currencyRate.toCurrency.name.
  4. Click next to the Measure to configure the chart's aggregation:
    • For Label, enter Total Sales - US Dollars.
    • For Aggregation Function, select Sum of.
    • For Field, remove the existing field selection. Then, use the dropdown to select the totalDue field.
  5. Return to the Bar Chart component configuration.
  6. Click FILTER RECORDS. We'll use a filter to only display year-to-date sales.
  7. Click Add Filter and configure the following conditions:
    • For Field, select orderDate.
    • For Condition, select Date Range.
    • For Value, select Year-to-Date.
  8. Click OK.
  9. For Sort, click ADD SORT.
  10. For Sort By:
    • Leave the default selection Alias.
    • Use the dropdown to select the totalDue_sum_measure1 alias.
    • Leave the default order of Descending.
  11. For Label, enter Sales by Currency Type (YTD).

The chart currently looks like this:

images/sales-by-currency1.png

Similar to the first chart, this chart has a missing value for "US Dollars".

However, unlike our first chart, we don't need to display "US Dollars" as a grouping. Since this chart is meant to display sales in each local currency compared to sales in US dollars, we'll add a second measure so account managers can quickly compare the sales numbers side-by-side.

As such, we can remove the [Category 1] value from our chart.

Filter out null categories

To remove the [Category 1] value, we’ll add another filter on the chart so that only orders that have a currency rate are returned.

We can easily filter our data by filtering directly on the relationship reference recordType!Order.relationships.currencyRate.

You can use a record type relationship reference in the field parameter of a!queryFilter() when the operator is set to "is null" or "not null". This allows you to only return records that do or do not have any related records.

To filter out null categories:

  1. Click FILTER RECORDS.

  2. Select Expression.

    Note:  You can only filter by a relationship in expression mode.

  3. Update the expression with the following:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

       a!queryLogicalExpression(
       operator: "AND",
       filters: {
       a!queryFilter(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
            operator: "between",
            value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
         )`,`
       `a!queryFilter(`
            `field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate',`
            `operator: "not null"`
         `)`
       },
       ignoreFiltersWithEmptyValues: true
     )
  4. Click OK.

The chart now only displays orders that have a currency rate of not null, which removes the [Category 1] bar.

images/sales-by-currency2.png

Create a custom record field to calculate currency conversion

Right now, our chart only displays the total sales generated in US dollars. To display the amount of sales generated in each local currency, we’ll create a custom record field on the Order record type that converts sales in US dollars to sales in the local currency.

To calculate this, we'll create another custom record field that evaluates in real-time.

We'll use the a!customFieldMultiply() function to multiply the order total by the daily average currency rate. However, the averageRate field will have a null value for US Dollars because there is no rate conversion between US Dollars and US Dollars. To prevent the function from returning a null value, we'll use the a!customFieldDefaultValue() function to replace null values with the total due in US Dollars.

To create this custom record field:

  1. Return to the Order record type.
  2. Click NEW CUSTOM RECORD FIELD.
  3. From SELECT A TEMPLATE, choose Write Your Own Expression.
  4. In the right-hand pane, select Real-time evaluation. Since we want to reference a related record field in our calculation, we need the field to evaluate in real-time.
  5. Click NEXT.

  6. Copy and paste the following expression in the Expression dialog:

    1
2
3
4
5
6
7
8
9

       a!customFieldDefaultValue(
       value: a!customFieldMultiply(
            value: {
                 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
                 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate.fields.{10dd27ed-6d31-49be-835f-42aaf1e6fa93}averageRate'
            }
       ),
       default: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue'
    )
  7. Click TEST to preview the results.
  8. Click NEXT.
  9. For Name, enter totalInLocalCurrency.
  10. Click CREATE. The new field totalInLocalCurrency appears in the list of fields available on the Order record type.
  11. Click SAVE CHANGES.
  12. Close the Order record type.

Add the custom record field as a second measure

In this last step, we'll add our new custom record field as a second measure in the chart.

To add the custom record field as a second measure:

  1. Return to the AR_SalesByCountryAndCurrencyPattern interface.
  2. In the Sales by Currency Type (YTD) bar chart, click ADD MEASURE.
  3. Click next to the second Measure to configure the chart's aggregation:
    • For Label, enter Total Sales - Local Currency.
    • For Aggregation Function, select Sum of.
    • For Field, select totalInLocalCurrency.
  4. Click SAVE CHANGES.

The final report now looks like this:

images/final-sales-by-currency-report.png

Full expression

The resulting expression will look like this:

Note:  To copy and paste this expression into an interface in the Appian Retail application, you must create the custom record field to replace null values and the custom record field to calculate currency conversion.

These record type references are specific to the Appian Retail application, so you will only need to replace the record field references if you're following along in a different environment.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

{
  a!columnsLayout(
    columns: {
      a!columnLayout(
        contents: {
          a!columnChartField(
            data: a!recordData(
              recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
              filters: a!queryLogicalExpression(
                operator: "AND",
                filters: {
                  a!queryFilter(
                    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
                    operator: "between",
                    value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
                  )
                },
                ignoreFiltersWithEmptyValues: true
              )
            ),
            config: a!columnChartConfig(
              primaryGrouping: a!grouping(
                field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{ae938f73-f61f-483d-8ca0-65d3c911abae}salesRegion.relationships.{0ea75675-bc93-491c-8cc2-3c74668c96d3}country.fields.{85846423-5eb5-46c0-ac1a-263e4f522be7}name',
                alias: "name_primaryGrouping"
              ),
              secondaryGrouping: a!grouping(
                field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{c7ed6acb-eba6-4a36-9558-1e30302ce33d}currency',
                alias: "currency_secondaryGrouping"
              ),
              measures: {
                a!measure(
                  function: "SUM",
                  field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
                  alias: "totalDue_sum_measure1"
                )
              },
              sort: {
                a!sortInfo(field: "totalDue_sum_measure1")
              },
              dataLimit: 100
            ),
            label: "Sales by Country in US Dollars (YTD)",
            stacking: "NORMAL",
            showLegend: true,
            showTooltips: true,
            labelPosition: "ABOVE",
            colorScheme: "CLASSIC",
            height: "MEDIUM",
            xAxisStyle: "STANDARD",
            yAxisStyle: "STANDARD",
            refreshAfter: "RECORD_ACTION"
          )
        }
      ),
      a!columnLayout(
        contents: {
          a!barChartField(
            data: a!recordData(
              recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
              filters: a!queryLogicalExpression(
                operator: "AND",
                filters: {
                  a!queryFilter(
                    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
                    operator: "between",
                    value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
                  ),
                  a!queryFilter(
                    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate',
                    operator: "not null"
                  )
                },
                ignoreFiltersWithEmptyValues: true
              )
            ),
            config: a!barChartConfig(
              primaryGrouping: a!grouping(
                field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{e84d7c2c-3d92-49ad-a683-3d77c4287c14}currencyRate.relationships.{49d04656-5c49-4793-beab-acff7bf3ff9b}toCurrency.fields.{3355f795-4a22-413e-a1bc-5d22269e4ae4}name',
                alias: "name_primaryGrouping"
              ),
              measures: {
                a!measure(
                  label: "Total Sales - US Dollars",
                  function: "SUM",
                  field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
                  alias: "totalDue_sum_measure1"
                ),
                a!measure(
                  label: "Total Sales - Local Currency",
                  function: "SUM",
                  field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{547d0dd0-730a-4c0b-8b25-e9347d5ac475}totalInLocalCurrency',
                  alias: "totalInLocalCurrency_sum_measure2"
                )
              },
              sort: {
                a!sortInfo(field: "totalDue_sum_measure1")
              },
              dataLimit: 100
            ),
            label: "Sales by Currency Type (YTD)",
            labelPosition: "ABOVE",
            stacking: "NONE",
            showLegend: true,
            showTooltips: true,
            colorScheme: "CLASSIC",
            height: "MEDIUM",
            xAxisStyle: "STANDARD",
            yAxisStyle: "STANDARD",
            refreshAfter: "RECORD_ACTION"
          )
        }
      )
    }
  )
}

Feedback