Free cookie consent management tool by TermsFeed Percentage of Online Sales [Build Reports from Records]
Percentage of Online Sales

This pattern illustrates how to calculate the percent of sales generated from online orders and display it in a gauge component. 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 yearly sales are generated from online sales so they can determine if they need to do more online advertising, or hire more in-person staff.

To show the percentage of online sales, you’ll use the pattern on this page to create a query using a!queryRecordType() to calculate the sum of sales for all orders purchased this year and the sum of sales for orders purchased online this year. Then, you'll uses a gauge component to calculate and display the percentage of online sales generated this year.

To allow account managers to better understand whether online sales are growing, stagnant, or decreasing, you'll also create a second gauge component that shows the percentage of online sales generated last year.

The report will look like this:

images/ytd-and-ly-ytd.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 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.

Create this pattern

To create this pattern, you will:

  1. Get the sum of sales for all orders purchased this year, and the sum of sales for orders purchased online this year.
  2. Calculate the percentage of online sales this and display it in a gauge component.
  3. Get the sum of sales for all orders purchased last year, and the sum of sales for orders purchased online last year.
  4. Calculate the percentage of online sales last year and display it in another gauge component.

Step 1: Get the sum of sales for all orders purchased this year, and the sum of sales for orders purchased online this year

Your first step is to get the sum of sales for all orders purchased this year, and the sum of sales for orders purchased online this year. To calculate these values, you'll create a query using a!queryRecordType() and save the value in a local variable within an interface.

Within the query, you'll filter the data so you only return records from the beginning of the year to today. Then, you'll group by the year when the order was made, and calculate two measures: the sum of all orders, and the sum of orders that have the onlineOrderFlag equal to 1, where 1 means the order was made online, and 0 means the order was made in-stores.

We'll use this query in the next step to calculate the percentage in the gauge component.

To calculate these values:

  1. In the Appian Retail application, go to the Build view.
  2. Click NEW > Interface.
  3. Click EXPRESSION MODE in the title bar.
  4. Copy and paste the following expression. This creates a local variable with our query, and includes a column layout that we'll use later in this pattern:

    Note:  These record type references are specific to the Appian Retail application. If you're following along in the Appian Retail application, you can copy and paste this expression without updating the record type references.

    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

     a!localVariables(
     local!onlineSalesTY: a!queryRecordType(
         recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
         /* Only include orders created this year: year-to-date */
         filters: {
         a!queryFilter(
             field:'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
             operator: "BETWEEN",
             value: {
             /* Beginning of the year */
             eomonth(today(), -month(today()) ) + 1,          
             now()
             }
         )
         },
         fields: {
         a!aggregationFields(
             /* Group by the order date year */
             groupings: a!grouping(
             field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
             interval: "YEAR",
             alias: "orderDateYear"
             ),
             measures: {
             /* Get the sum of all orders */
             a!measure(
                 field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
                 function: "SUM",
                 alias: "totalSales"
             ),
             /* Get the sum of all orders that have an online order flag */
             a!measure(
                 field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
                 function: "SUM",
                 alias: "onlineSales",
                 filters: {
                 a!queryFilter(
                     field: 'recordType!{bec4a875-9980-4bbf-a38c-c492ebed065a}Order Detail.relationships.{e6b1dbca-6c3c-4540-a093-3c581a73ad17}order.fields.{5bade7d5-5fbc-4cc4-807f-907f8f65969b}onlineOrderFlag',
                     operator: "=",
                     value: 1
                 ),
                 }
             )
             }
         )
         },
         pagingInfo: a!pagingInfo(1,10)
     ).data,
     /* Column layout that we'll use for our gauge components */
     {
         a!columnsLayout(
         columns: {
             a!columnLayout(contents: {}),
             a!columnLayout(contents: {})
         }
         )
     }
 )

Step 2: Show the percentage of online sales this year

Now that you have a query that calculates your total sales this year and sales from online orders this year, you can calculate the percentage of online sales directly in a gauge component.

To calculate the percentage of online sales this year in a gauge component:

  1. In your interface, click DESIGN MODE in the title bar. A column layout with two columns appears.
  2. From the PALETTE, drag a GAUGE component into the left column layout.
  3. In the Gauge component configuration, hover over Fill Percentage and click Edit as Expression .

  4. In the Fill Percentage dialog, replace the existing value with the following expression. This will calculate the percent of online sales:

    1

     local!onlineSalesTY.onlineSales / local!onlineSalesTY.totalSales * 100
  5. Click OK.
  6. In Secondary Text, enter YTD. This stands for year to date.
  7. Hover over Tooltip and click Edit as Expression .

  8. In the Tooltip dialog, enter the following expression. This will display the sum of sales for this year.

    1
2
3
4

    "Online Sales: " & a!currency(
  isoCode: "USD",
  value: a!defaultValue(local!onlineSalesTY.onlineSales,0)
)
  9. Click OK.

The interface will look something like this:

Single gauge component with this year's percent of online sales

Note:  Your percentage may differ from the image above since the query in local!onlineSalesTY uses the today() function, which will change the calculation each day.

Step 3: Get the sum of sales for all orders purchased last year, and the sum of sales for orders purchased online last year

Now you need to calculate last year's percentage of online sales.

To get this percentage, you first need to gets the sum of sales for all orders purchased last year, as well as the sum of sales for orders purchased online last year.

This query will look similar to the one you created in step 1, but instead of filtering by sales from the beginning of the year to today (eomonth(today(), -month(today()) ) + 1), you will filter sales data from the beginning of last year to today's date last year (datetime(year(eomonth(today(), -month(today()) ) + 1) - 1, 1, 1)).

To calculate these values:

  1. In your interface, click EXPRESSION MODE in the title bar.
  2. In the Interface Definition, enter a new line after line 48.

  3. Copy and paste the following expression on line 49. This creates a second local variable with our second query:

    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

     local!onlineSalesLY: a!queryRecordType(
     recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
     /* Only include orders created last-year-to-date */
     filters: {
     a!queryFilter(
         field:'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
         operator: "BETWEEN",
         value: {
         /* Beginning of the year - Last year */
         datetime(year(eomonth(today(), -month(today()) ) + 1) - 1, 1, 1),
         /* Today - Last year */
         datetime(year(today()) -1,  month(today()), day(today()))
         }
     )
     },
     fields: {
     a!aggregationFields(
         /* Group by the order date year */
         groupings: a!grouping(
         field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
         interval: "YEAR",
         alias: "orderDateYear"
         ),
         measures: {
         /* Get the sum of all orders */
         a!measure(
             field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}salesOrderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
             function: "SUM",
             alias: "totalSalesLY"
         ),
         /* Get the sum of all orders that have an online order flag */
         a!measure(
             field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}salesOrderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
             function: "SUM",
             alias: "onlineSalesLY",
             filters: {
             a!queryFilter(
                 field: 'recordType!{bec4a875-9980-4bbf-a38c-c492ebed065a}Order Detail.relationships.{e6b1dbca-6c3c-4540-a093-3c581a73ad17}salesOrderHeader.fields.{5bade7d5-5fbc-4cc4-807f-907f8f65969b}onlineOrderFlag',
                 operator: "=",
                 value: 1
             ),
             }
         )
         }
     )
     },
     pagingInfo: a!pagingInfo(1,10)
 ).data,

Step 4: Show the percentage of online sales last year

Now that you have your query, you'll add a second gauge component to calculate and display last year's online sales percentage.

To calculate the percentage of online sales last year in a gauge component:

  1. In your interface, click DESIGN MODE in the title bar.
  2. From the PALETTE, drag a GAUGE component into the right column layout.
  3. In the Gauge component configuration, hover over Fill Percentage and click Edit as Expression .

  4. In the Fill Percentage dialog, replace the existing value with the following expression:

    1

     local!onlineSalesLY.onlineSalesLY / local!onlineSalesLY.totalSalesLY * 100
  5. Click OK.
  6. In Secondary Text, enter LY YTD. This stands for last year, year to date.
  7. Hover over Tooltip and click Edit as Expression .

  8. In the Tooltip dialog, enter the following expression:

    1
2
3
4

    "Online Sales: " & a!currency(
  isoCode: "USD",
  value: a!defaultValue(local!onlineSalesLY.onlineSalesLY,0)
)
  9. Click OK.
  10. From the PALETTE, drag a RICH TEXT component above the columns layout and configure the following:
    • In Display Value, keep the default selection of Use editor.
    • In the editor, enter Percent of Online Sales.
    • In the editor, highlight the text, then click Size Size icon and select Medium Header.

  11. Click SAVE CHANGES.

    Gauge component showing percentage of online sales this year

Full expression

The resulting expression will look like this:

Note:  You can copy and paste this expression into an interface in the Appian Retail application to see the fully configured pattern.

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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

a!localVariables(
  local!onlineSalesTY: a!queryRecordType(
    recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
    /* Only include orders created this year: year-to-date */
    filters: {
      a!queryFilter(
        field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
        operator: "BETWEEN",
        value: {
          /* Beginning of the year */
          eomonth(today(), - month(today())) + 1,
          now()
        }
      )
    },
    fields: {
      a!aggregationFields(
        /* Group by the order date year */
        groupings: a!grouping(
          field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
          interval: "YEAR",
          alias: "orderDateYear"
        ),
        measures: {
          /* Get the sum of all orders */
          a!measure(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
            function: "SUM",
            alias: "totalSales"
          ),
          /* Get the sum of all orders that have an online order flag*/
          a!measure(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
            function: "SUM",
            alias: "onlineSales",
            filters: {
              a!queryFilter(
                field: 'recordType!{bec4a875-9980-4bbf-a38c-c492ebed065a}Order Detail.relationships.{e6b1dbca-6c3c-4540-a093-3c581a73ad17}order.fields.{5bade7d5-5fbc-4cc4-807f-907f8f65969b}onlineOrderFlag',
                operator: "=",
                value: 1
              ),

            }
          )
        }
      )
    },
    pagingInfo: a!pagingInfo(1, 10)
  ).data,
  local!onlineSalesLY: a!queryRecordType(
    recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
    filters: {
      a!queryFilter(
        field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
        operator: "BETWEEN",
        value: {
          /*Beginning of the year - Last year*/
          datetime(
            year(eomonth(today(), - month(today())) + 1) - 1,
            1,
            1
          ),
          /*Today - Last year */
          datetime(
            year(today()) - 1,
            month(today()),
            day(today())
          )
        }
      )
    },
    fields: {
      a!aggregationFields(
        /* Group by the order date year */
        groupings: a!grouping(
          field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
          interval: "YEAR",
          alias: "orderDateYear"
        ),
        measures: {
          /* Get the sum of all orders */
          a!measure(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
            function: "SUM",
            alias: "totalSalesLY"
          ),
          /* Get the sum of all orders that have an online order flag*/
          a!measure(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.fields.{db456082-5f77-4765-bc3e-f662651e0d52}lineTotal',
            function: "SUM",
            alias: "onlineSalesLY",
            filters: {
              a!queryFilter(
                field: 'recordType!{bec4a875-9980-4bbf-a38c-c492ebed065a}Order Detail.relationships.{e6b1dbca-6c3c-4540-a093-3c581a73ad17}order.fields.{5bade7d5-5fbc-4cc4-807f-907f8f65969b}onlineOrderFlag',
                operator: "=",
                value: 1
              ),

            }
          )
        }
      )
    },
    pagingInfo: a!pagingInfo(1, 10)
  ).data,
  /* Column layout that we'll use for our gauge components*/
  {
    a!richTextDisplayField(
      labelPosition: "COLLAPSED",
      value: {
        a!richTextHeader(text: { "Percent of Online Sales" })
      }
    ),
    a!columnsLayout(
      columns: {
        a!columnLayout(
          contents: {
            a!gaugeField(
              labelPosition: "COLLAPSED",
              percentage: local!onlineSalesTY.onlineSales / local!onlineSalesTY.totalSales * 100,
              primaryText: a!gaugePercentage(),
              secondaryText: "YTD",
              tooltip: "Online Sales: " & a!currency(isoCode: "USD", value:
                a!defaultValue(local!onlineSalesTY.onlineSales, 0)
              )
            )
          }
        ),
        a!columnLayout(
          contents: {
            a!gaugeField(
              labelPosition: "COLLAPSED",
              percentage: local!onlineSalesLY.onlineSalesLY / local!onlineSalesLY.totalSalesLY * 100,
              primaryText: a!gaugePercentage(),
              secondaryText: "LY YTD",
              tooltip: "Online Sales: " & a!currency(isoCode: "USD", value:
                a!defaultValue(local!onlineSalesLY.onlineSalesLY, 0)
              )
            )
          }
        )
      }
    )
  }
)

Percentage of Online Sales

FEEDBACK