This pattern illustrates how to create a column chart that compares the number of total orders and the number of orders that had at least one item purchased with a promo code. 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.
Account managers at the Appian Retail company want to know: of the total number of orders placed this year, how many orders had at least one item purchased with a promo code? Account managers will use this information to see if promo codes are having a positive effect on their total number of sales. Depending on the data, they may choose to send more promo codes during lower performing months to boost sales.
To allow account managers to analyze the relationship between promo codes and total orders, you'll use the pattern on this page to create a column chart that shows the count of total orders and the count of orders that contain at least one promo code. To get a full year's worth of data, you'll filter the chart so it only includes orders from the past 12 months.
The report will look like this:
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:
To create this pattern, you will:
To create the column chart for this pattern:
orderDate.Total Orders.Click OK.
The chart currently displays the count of orders placed over the last 12 months. Now, we're going to add a second measure with a filter on the measure so it displays the count of orders that have at least one item purchased with a promo code.
Tip: Notice that the filter will return orders with at least one item purchased with a promo code. This at least one behavior occurs because there is a one-to-many relationship between where you are aggregating and where you are filtering. Learn more about "at least one" filter behavior.
To add the second measure:
Orders with Promo Codes.No Discount.Click OK.
In this last step, you'll add a chart label, change the color scheme, and hide the Y-Axis labels.
To format the chart:
Order Trends Over the Last 12 Months.For Y-Axis Style, choose None.
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
{
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: /* Trailing 12 Months */toDatetime(
{
eomonth(today(), - 13) + 1,
eomonth(today(), - 1) + 1
}
)
)
},
ignoreFiltersWithEmptyValues: true
)
),
config: a!columnChartConfig(
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"
),
/* Count of all orders */
measures: {
a!measure(
label: "Total Orders",
function: "COUNT",
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{262bb249-cf34-4171-a573-54831d0958dd}orderId',
alias: "orderId_count_measure1"
),
/* Count of orders with at least one item containing a promo code */
a!measure(
label: "Orders with Promo Codes",
function: "COUNT",
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{262bb249-cf34-4171-a573-54831d0958dd}orderId',
filters: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{0bde4028-fd7a-411f-97ad-7ad5b84e0d18}orderDetail.relationships.{2ca679d6-3800-419b-8310-c1e328897e60}promoCode.fields.{aad6d5cd-e48f-46b4-bb00-a8252d51b3ee}description',
operator: "<>",
value: "No Discount"
)
},
ignoreFiltersWithEmptyValues: true
),
alias: "orderId_count_measure2"
)
},
dataLimit: 100
),
label: "Order Trends Over the Last 12 Months",
stacking: "NONE",
showLegend: true,
showTooltips: true,
labelPosition: "ABOVE",
colorScheme: "SUNSET",
height: "MEDIUM",
xAxisStyle: "STANDARD",
yAxisStyle: "NONE",
refreshAfter: "RECORD_ACTION"
)
}
Total Orders Compared to Orders Purchased with Promo Codes
