Dynamically Show Sales by Product Category Compared to Total Sales

This pattern illustrates how to create an area chart that dynamically displays sales generated by product category compared to total sales. 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

Inventory managers at the Appian Retail company want to know how monthly sales were divided among the different product categories to see how their line of products impact overall sales. Since each inventory manager is responsible for a certain product category, they want this report to help them determine if they need to change their inventory strategy to follow sales trends.

To allow different inventory managers to view their own product category sales, you'll use the pattern on this page to create an area chart that can be filtered by product category. This way, users can see their monthly sales for the selected category compared to the total sales generated each month.

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 stores on 5/31/2019 and the order is closed.
• Order Detail record type: Contains specific order details like the number of order items, order totals, promo codes applied, and products. For example, the order above contained one product that cost $2,024.99.
• Product Category record type: Contains product categories. For example, Bikes.

Create this pattern

To create this pattern, you will:

1. Create an area chart with total sales per month.
2. Add a second measure with a filter on product category to the chart.
3. Add a dropdown component with product categories.
4. Create a local variable to dynamically filter the chart based on the dropdown selection.

Step 1: Create an area chart with total sales per month

First, we'll create an area chart that shows the total sales generated each month in 2021.

To create the area chart:

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 an AREA CHART component into the interface.
5. From Data Source, select RECORD TYPE and search for the Order record type.
6. For Primary Grouping, keep the default field selection orderDate.
7. Click the pencil icon next to the Primary Grouping to change the format of the date values. This will allow you to read the dates in an easier format:
   • For Time Interval, select Month.
   • For Format Value, use a pre-defined format to display the abbreviated month and year. For example, Nov 2021.
8. Return to the Area Chart component configuration.
9. Click the pencil icon next to the Measure to configure the chart's aggregation:
   • For Label, enter Total Sales.
   • For Aggregation Function, select Sum of.
   • For Field, remove the existing field. Then, use the dropdown to hover over the orderDetail relationship and select the lineTotal field. The field will display as orderDetail.lineTotal.
10. Return to the Area Chart component configuration.
11. Click FILTER RECORDS.
12. 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.

13. Click OK.

14. Click OK to close the dialog.

    

    

Step 2: Add a second measure with a filter

Now, we're going to add a second measure to the area chart. This measure will include a filter so it only displays the sum of sales from orders that have at least one item from the Bikes category.

To add the second measure:

1. On the area chart, click ADD MEASURE.
2. Click the pencil icon next to the new Measure to configure the second aggregation:
   • For Label, enter Product Category: Bikes.
   • For Aggregation Function, select Sum of.
   • For Field, use the dropdown to hover over the orderDetail relationship and select the lineTotal field. The field will display as orderDetail.lineTotal.
3. Click + ADD FILTERS.
4. Click Add Filter and configure following:
   1. For Field, use the dropdown to hover over product > productSubcategory > productCategory and select the name field. The field will display as product.productSubcategory.productCategory.name.
   2. For Condition, leave the default selection of equal to.
   3. For Value, enter Bikes.
5. Click OK.
6. Return to the Area Chart component configuration.
7. For Label, enter Monthly Sales in 2021.

8. For Stacking, select None.

   

   

Step 3: Add a dropdown component

Since inventory managers want to see sales trends for each product category, not just bikes, you'll add a filter that lets users change which product category is displayed in the chart.

To dynamically filter the chart, you'll add a dropdown component and configure a local variable so the selected dropdown option will filter the chart. Let's start by adding the dropdown component.

To add the dropdown component:

1. From the PALETTE, drag the COLUMNS component onto your interface above the area chart.
2. From the PALETTE, drag the DROPDOWN component into the left column.
3. For the dropdown's Label, enter Product Categories.
4. Hover over Choice Labels and click Edit as Expression . The Choice Labels dialog appears.
5. Delete the default values in the dialog.
6. Click Create Constant and configure the following values:
   • For Name, enter AR_CATEGORIES.
   • For Type, select Text.
   • Select the Array (multiple values) checkbox.
   • For Values, enter the category options. Separate each category by a line break, but do not include spaces, commas, or quotations:

     1
     2
     3
     4
     Bikes
     Accessories
     Clothing
     Components
     

   • Leave the other fields as default.
7. Click CREATE. The constant cons!AR_CATEGORIES appears in the Choice Labels dialog.
8. Click OK.

   Note:  After you change the Choice Label to use the constant, an error will appear. This is expected since the Choice Label and Choice Values fields currently have different values. The error will resolve itself when you change the Choice Values field to use the same constant.

9. Hover over Choice Values and click Edit as Expression . The Choice Values dialog appears.
10. Delete the default values in the Choice Values dialog and enter cons!AR_CATEGORIES.

11. Click OK.

    

    

Step 4: Add a local variable to filter the chart based on the dropdown selection

Now that you have a dropdown component, you need to configure a local variable to save the selected dropdown value. After, you'll configure a filter on the chart to only display sales by the selected value.

Since you want a category selection to appear on the chart when it first loads, you’ll also add a default value to your local variable.

To add the local variable:

1. In your interface, click EXPRESSION in the title bar.

2. Modify the Interface Definition by making the highlighted changes to the expression:

   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
   +   a!localVariables(
   +   local!category: "Bikes",
          {
            a!columnsLayout(
              columns: {
                a!columnLayout(
                  contents: {
                    a!dropdownField(
                      label: "Product Categories",
                      labelPosition: "ABOVE",
                      placeholder: "--- Select a Value ---",
                      choiceLabels: cons!AR_CATEGORIES,
                      choiceValues: cons!AR_CATEGORIES,
                      saveInto: {},
                      searchDisplay: "AUTO",
                      validations: {}
                    )
                  }
                ),
                a!columnLayout(contents: {}),
                a!columnLayout(contents: {})
              }
            ),
            a!lineChartField(
              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: /* Trailing 12 Months */toDatetime(
                        {
                          eomonth(today(), - 13) + 1,
                          eomonth(today(), - 1) + 1
                        }
                      )
                    )
                  },
                  ignoreFiltersWithEmptyValues: true
                )
              ),
              config: a!lineChartConfig(
                primaryGrouping: a!grouping(
                  field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
                  alias: "orderDate_month_primaryGrouping",
                  interval: "MONTH_SHORT_TEXT"
                ),
                measures: {
                  a!measure(
                    label: "Total Sales",
                    function: "SUM",
                    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
                    alias: "lineTotal_sum_measure1"
                  ),
                  a!measure(
                    label: "Product Category: Bikes",
                    function: "SUM",
                    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}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.{eee8c0a6-46b0-4cb7-9faf-be492400cc41}product.relationships.{d3a62d4a-3268-48dc-9563-3b99c33715d1}productSubcategory.relationships.{61e25c34-c4ba-4315-8da4-b2ed06d9b5ae}productCategory.fields.{963f051f-baea-4a23-8481-e365bf972a74}name',
                          operator: "=",
                          value: "Bikes"
                        )
                      },
                      ignoreFiltersWithEmptyValues: true
                    ),
                    alias: "lineTotal_sum_measure2"
                  )
                },
                dataLimit: 100
              ),
              label: "Monthly Sales ",
              labelPosition: "ABOVE",
              showLegend: true,
              showTooltips: true,
              colorScheme: "CLASSIC",
              height: "MEDIUM",
              xAxisStyle: "STANDARD",
              yAxisStyle: "STANDARD",
              refreshAfter: "RECORD_ACTION"
            )
          }
   +   )
   

Now that you have a local variable to store the selected category, we can update the dropdown component to use local!category as the saveInto value, and add a filter using local!category to filter the chart by category.

To use the local variable to filter the chart:

1. In the a!dropdownField() configuration (line 8), add the following parameter and values highlighted below:

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
    a!dropdownField(
        label: "Product Categories",
        labelPosition: "ABOVE",
        placeholder: `"--- Select a category ---"`,
        choiceLabels: cons!AR_CATEGORIES,
        choiceValues: cons!AR_CATEGORIES,
   +    value: local!category,
        saveInto: `local!category`,
        searchDisplay: "AUTO",
        validations: {}
    )
   

2. In the a!areaChartConfig() configuration (line 47), update the following values highlighted below:

   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
    ...
    config: a!areaChartConfig(
      primaryGrouping: a!grouping(
        field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
        alias: "orderDate_month_primaryGrouping",
        interval: "MONTH_TEXT"
      ),
      measures: {
        a!measure(
          label: "Total Sales",
          function: "SUM",
          field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
          alias: "lineTotal_sum_measure1"
        ),
        a!measure(
          label: `"Product Category: " & local!category`,
          function: "SUM",
          field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}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.{eee8c0a6-46b0-4cb7-9faf-be492400cc41}product.relationships.{d3a62d4a-3268-48dc-9563-3b99c33715d1}productSubcategory.relationships.{61e25c34-c4ba-4315-8da4-b2ed06d9b5ae}productCategory.fields.{963f051f-baea-4a23-8481-e365bf972a74}name',
                  operator: "=",
                  value: `local!category`
                )
              },
              ignoreFiltersWithEmptyValues: true
            ),
            alias: "lineTotal_sum_measure2"
        )
      ...
   

3. Click SAVE CHANGES.

The dropdown now filters the product sales displayed on the chart.

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
a!localVariables(
  local!category: "Bikes",
  {
    a!columnsLayout(
      columns: {
        a!columnLayout(
          contents: {
            a!dropdownField(
              label: "Product Categories",
              labelPosition: "ABOVE",
              placeholder: "--- Select a Value ---",
              choiceLabels: cons!AR_CATEGORIES,
              choiceValues: cons!AR_CATEGORIES,
              value: local!category,
              saveInto: local!category,
              searchDisplay: "AUTO",
              validations: {}
            )
          }
        ),
        a!columnLayout(contents: {}),
        a!columnLayout(contents: {})
      }
    ),
    a!areaChartField(
      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!areaChartConfig(
        primaryGrouping: a!grouping(
          field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
          alias: "orderDate_month_primaryGrouping",
          interval: "MONTH_SHORT_TEXT"
        ),
        measures: {
          a!measure(
            label: "Total Sales",
            function: "SUM",
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
            alias: "lineTotal_sum_measure1"
          ),
          a!measure(
            label: "Product Category: " & local!category,
            function: "SUM",
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}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.{eee8c0a6-46b0-4cb7-9faf-be492400cc41}product.relationships.{d3a62d4a-3268-48dc-9563-3b99c33715d1}productSubcategory.relationships.{61e25c34-c4ba-4315-8da4-b2ed06d9b5ae}productCategory.fields.{963f051f-baea-4a23-8481-e365bf972a74}name',
                  operator: "=",
                  value: local!category
                )
              },
              ignoreFiltersWithEmptyValues: true
            ),
            alias: "lineTotal_sum_measure2"
          )
        },
        dataLimit: 100,
        showIntervalsWithNoData: true
      ),
      label: "Monthly Sales in 2021",
      labelPosition: "ABOVE",
      stacking: "NONE",
      showLegend: true,
      showTooltips: true,
      colorScheme: "CLASSIC",
      height: "MEDIUM",
      xAxisStyle: "STANDARD",
      yAxisStyle: "STANDARD",
      refreshAfter: "RECORD_ACTION"
    )
  }
)

Feedback