This pattern illustrates how to create a bar chart that shows sales per sales region. 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

Sales executives at the Appian Retail company want to know which global sales regions have the highest sales numbers so they can hire more employees and dedicate more resources to high-performance areas.

To show the sales regions with the highest sales, you'll use the pattern on this page to create a bar chart that displays the sum of sales generated by each sales region this year. To see if sales have improved for certain regions, you'll also show the sum of sales generated by each region last year.

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:

• Sales Regions: Contains the geographical regions where sales teams are located. For example, the Northwest or Central territories in North America, Canada, France, and Germany.
• Country: Contains country names. For example, the United States or Australia.
• Order Detail record type: Contains specific order details like the number of order items, order totals, promo codes applied, and products. For example, an order contained one product that cost $2,024.99.

Create this pattern

To create this pattern:

1. Create a bar chart that shows sales per region.
2. Add a second measure to show last year's sales per region.
3. Sort the chart by this year's sales.
4. (Optional) Format the bar chart.

Step 1: Create a bar chart that shows sales per region

To create the bar chart for this pattern:

1. In the Appian Retail application, go to the Build view.
2. Click NEW > Interface.
3. From the PALETTE, drag a BAR CHART component into the interface.
4. From Data Source, select RECORD TYPE and search for the Sales Region record type.
5. For Primary Grouping, remove the existing field. Then, use the dropdown to hover over the country relationship and select the name field. This displays the country of each sales region.
6. Click the pencil icon next to the Measure to configure the chart's aggregation:
   • For Label, enter YTD. This will represent the year-to-date sales.
   • For Aggregation Function, select Sum of.
   • For Field, remove the existing field. Then, use the dropdown to hover over order > orderDetail and select the lineTotal field. The field will display as order.orderDetail.lineTotal.
   • For Alias, enter ytd.
7. Click + ADD FILTERS.
8. Click Add Filter and configure a filter so only sales from this year are calculated in the measure:
   • For Field, use the dropdown to hover over the order relationship and select the orderDate field. The field will display as order.orderDate.
   • For Condition, select Date Range.
   • For Value, select Year-to-Date.

9. Click OK.

   

   

Step 2: Add last year's sales

The chart currently displays the sum of sales from orders placed at the beginning of the year to today. Now, you're going to add a second measure with a different filter so it displays the sum of sales from orders placed at the beginning of last year to today's date last year.

To add last year's sales:

1. Return to the Bar Chart component configuration.
2. Click ADD MEASURE.
3. Click the pencil icon next to the Measure to configure the second aggregation:
   • For Label, enter LY YTD. This will represent last year's year-to-date sales.
   • For Aggregation Function, select Sum of.
   • For Field, use the dropdown to hover over order > orderDetail and select the lineTotal field. The field will display as order.orderDetail.lineTotal.
   • For Alias, enter lytd.
4. Click + ADD FILTERS.
5. Click Add Filter and configure a filter so only sales from last year are calculated in the measure:
   • For Field, use the dropdown to hover over the order relationship and select the orderDate field. The field will display as order.orderDate.
   • For Condition, select Date Range.
   • For Value, select Previous Year-to-Date (From Beginning of Last Year to Today Last Year).

6. Click OK.

   

   

Step 3: Sort by this year's sales

To allow executives to quickly see the top performing sales regions, you'll sort the chart by the highest sales this year.

To sort the chart:

1. Return to the Bar Chart component configuration.
2. Click ADD SORT.
3. For Sort By, leave the default selection of Alias.
4. Use the dropdown to choose the ytd alias.

5. For Order, keep the default selection of Descending.

   

   

Optional chart formatting

In this last step, you'll add a chart label, change the color scheme, and hide the Y-Axis labels since they are truncated.

To format the chart:

1. For Label, enter Sales by Region.
2. For Color Scheme, keep the default Choose pre-defined color scheme, and choose Midnight from the dropdown.

3. For Y-Axis Style, choose None.

   

   

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
{
  a!barChartField(
    data: 'recordType!{c25947c0-2230-41cb-86a6-bd86d14d0af9}Sales Region',
    config: a!barChartConfig(
      primaryGrouping: a!grouping(
        field: 'recordType!{c25947c0-2230-41cb-86a6-bd86d14d0af9}Sales Region.relationships.{0ea75675-bc93-491c-8cc2-3c74668c96d3}country.fields.{85846423-5eb5-46c0-ac1a-263e4f522be7}name',
        alias: "name_primaryGrouping"
      ),
      measures: {
        a!measure(
          label: "YTD",
          function: "SUM",
          field: 'recordType!{c25947c0-2230-41cb-86a6-bd86d14d0af9}Sales Region.relationships.{91b8d190-173e-407a-bf9b-e42e8e32a437}order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
          filters: a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: 'recordType!{bec4a875-9980-4bbf-a38c-c492ebed065a}Order Detail.relationships.{e6b1dbca-6c3c-4540-a093-3c581a73ad17}order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
                operator: "between",
                value: /* Year-to-Date */{ datetime(year(today()), 1, 1), now() }
              )
            },
            ignoreFiltersWithEmptyValues: true
          ),
          alias: "ytd"
        ),
        a!measure(
          label: "LY YTD",
          function: "SUM",
          field: 'recordType!{c25947c0-2230-41cb-86a6-bd86d14d0af9}Sales Region.relationships.{91b8d190-173e-407a-bf9b-e42e8e32a437}order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
          filters: a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: 'recordType!{bec4a875-9980-4bbf-a38c-c492ebed065a}Order Detail.relationships.{e6b1dbca-6c3c-4540-a093-3c581a73ad17}order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
                operator: "between",
                value: /* Previous Year-to-Date (From Beginning of Last Year to Today Last Year) */{
                  datetime(year(today()) - 1, 1, 1),
                  edate(today(), - 12) + (now() - today())
                }
              )
            },
            ignoreFiltersWithEmptyValues: true
          ),
          alias: "lytd"
        )
      },
      sort: { a!sortInfo(field: "ytd") },
      dataLimit: 100
    ),
    label: "Sales by Region",
    labelPosition: "ABOVE",
    stacking: "NONE",
    showLegend: true,
    showTooltips: true,
    colorScheme: "MIDNIGHT",
    height: "MEDIUM",
    xAxisStyle: "STANDARD",
    yAxisStyle: "NONE",
    refreshAfter: "RECORD_ACTION"
  )
}