Requirement: Create a filter in the dashboard that bucket sums the Amount of customers into categories, such as low (< $500), medium ($500 - $1000), and high (> $1000). The result should be updated dynamically as filtered when other widgets are selected. The range of the Sum of Amount should be fixed, not depend on the Sum of Amount values in the data.
Solution: As each user can select different filters, such as Region, Product, etc., we can't stamp the sum of Amount in the dataflow/recipe. Binding is here to help.
Step-by-step:
1. Create Custom Query
- We need "All" as the 1st value. This is when the dashboard loads; it will load all data. Remember to select the list widget selection type as "Single selection (required)".
- The max value for All and the high (last) row can be set as high as possible.
2. Add Table Widget
- Change the table to Compare Table
- Group By: Customer or Account name
- Change Column to Sum of Amount
- Add a Filter to the Sum of Amount column, use the medium range, which is between 500 and 1000
3. Add Binding to the Filter Range
- Select the table widget added.
- Click Advanced Editor >> Query tab
- Look for "aggregateFilters" with values 500 and 1000
- Change 500, 1000 to the following
"{{coalesce(cell(static1_1.selection, 0, \"low\"), cell(static1_1.result, 0, \"low\")).asObject()}}",
"{{coalesce(cell(static1_1.selection, 0, \"high\"), cell(static1_1.result, 0, \"high\")).asObject()}}"
- Save the query and done :)
Result
See the table widget
no filter
6 months ago to today
6 months ago to today, and the Sum of Amount between $500 - $1000
Product X3 and X4, and the Sum of Amount between $500 - $1000
No comments:
Post a Comment