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 see a breakdown of their 2021 sales 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) by 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:

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.

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 interface properties and click CREATE.
4. From the PALETTE, drag a COLUMNS component into the interface.
5. 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:

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 2021 sales (calculated in US 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:

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.

   

   

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. Click the pencil icon next to the Measure to configure the chart's aggregation:
   • For Aggregation Function, select Sum of.
   • For Field, remove the existing field. Then, select the totalDue field.
   • For Format Value, use the dropdown to select Dollar.
5. Return to the Column Chart configuration.
6. Click FILTER RECORDS. We'll use a filter to only display 2021 sales.
7. Click Add Filter and configure the following conditions:
   • For Field, select orderDate.
   • For Condition, select Date Range.
   • For Value, use the context menu () to select Expression.
   • Click null to edit the expression.
   • Replace the existing value with the following expression:

   1
   2
   3
   4
      {
        todatetime("01/01/2021"),
        todatetime("12/31/2021")
      }
   

   Tip:  In your own applications, you can use Date Presets to dynamically filter data. This pattern uses a static date for testing purposes.

8. Click OK.
9. Click OK to close the dialog.
10. For Sort, click ADD SORT.
11. For Sort By:
    • Leave the default selection Alias.
    • Use the dropdown to select the totalDue_sum_measure1 alias.
    • Leave the default order of Descending.
12. For Label, enter Sales by Country in US Dollars (2021).

The chart currently looks like this:

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:

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. This custom record field will 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 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:

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 2021 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:

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 2021 sales.
7. Click Add Filter and configure the following conditions:
   • For Field, select orderDate.
   • For Condition, select Date Range.
   • For Value, use the context menu () to select Expression.
   • Click null to edit the expression.

   • Replace the existing value with the following expression:

     1
     2
     3
     4
     {
       todatetime("01/01/2021"),
       todatetime("12/31/2021")
     }
     

   Tip:  In your own applications, you can use Date Presets to dynamically filter data. This pattern uses a static date for testing purposes.

8. Click OK.
9. Click OK to close the dialog.
10. For Sort, click ADD SORT.
11. For Sort By:
    • Leave the default selection Alias.
    • Use the dropdown to select the totalDue_sum_measure1 alias.
    • Leave the default order of Descending.
12. For Label, enter Sales by Currency Type (2021).

The chart currently looks like this:

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 will remove the [Category 1] value from our chart in the next step.

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. Replace the expression with the following:

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
    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: {
            todatetime("01/01/2021"),
            todatetime("12/31/2021")
          }
        ),
        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.

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 another 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 a 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 interface.
2. In the Sales by Currency Type (2021) 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:

Full expression

The resulting expression will look like this:

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
116
117
118
119
120
121
122
123
124
{
  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: {
                      todatetime("01/01/2021"),
                      todatetime("12/31/2021")
                    }
                  )
                },
                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"
              ),
              /*  Replace this field with the currency custom record field in your application */
              secondaryGrouping: a!grouping(
                field: recordType!Order.fields.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",
                  formatValue: "DOLLAR"
                )
              },
              sort: {
                a!sortInfo(field: "totalDue_sum_measure1")
              },
              dataLimit: 100
            ),
            label: "Sales by Country in US Dollars (2021)",
            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: {
                      todatetime("01/01/2021"),
                      todatetime("12/31/2021")
                    }
                  ),
                  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"
                ),
                /* Replace this field with the totalInLocalCurrency custom record field in your application */
                a!measure(
                  label: "Total Sales - Local Currency",
                  function: "SUM",
                  field: recordType!Order.fields.totalInLocalCurrency,
                  alias: "totalInLocalCurrency_sum_measure2"
                )
              },
              sort: {
                a!sortInfo(field: "totalDue_sum_measure1")
              },
              dataLimit: 100
            ),
            label: "Sales by Currency Type (2021)",
            labelPosition: "ABOVE",
            stacking: "NONE",
            showLegend: true,
            showTooltips: true,
            colorScheme: "CLASSIC",
            height: "MEDIUM",
            xAxisStyle: "STANDARD",
            yAxisStyle: "STANDARD",
            refreshAfter: "RECORD_ACTION"
          )
        }
      )
    }
  )
}

Feedback