- 1. How to create fixed aggregation columns
- 1.a) Overview
- 1.b) Grand total of sales
- 1.c) Sales by state
- 1.d) Perform ratios
- 2. How to filter fixed aggregation columns without filtering the view
- 2.a) Overview
- 2.b) Add and edit filters
- 2.c) Synchronize fixed aggregation columns with the view filters
- 2.d) Recursive fixed aggregation
- 3. INCLUDE aggregation
- 4. EXCLUDE aggregation
- 5. GROUPING features
- 5.a) GROUP aggregation
- 5.b) GROUP ‘OTHER’
1. How to create fixed aggregation columns
1.a) Overview
Imagine you have a view displaying sales data by State then City. You need to perform:
- The ratio of sales in a city to the sales in its related state.
- The ratio of sales in a state to the total sales.
In Excel, selecting cells and calculating ratios with respect to parent groups or totals is straightforward. However, when working with databases, we need to bring the parent aggregation or total down to the row level, and this is where linked views come into play in KAWA.
Indeed in KAWA, the solution to perform these calculations is to create 2 new columns that store fixed aggregations and then use these columns in your calculations:
- “Grand total of sales” columns
- "Sum of Sales by State" column
1.b) Grand total of sales
Create a new column “Grand Total of sales” containing the total sales on all rows.
1.c) Sales by state
Create a new column “Sales by state” containing the sales in related states for each city.
1.d) Perform ratios
Now that you have these new columns, you can easily perform calculations like the ratio of "Sales by State" to "Grand Total of Sales”.
2. How to filter fixed aggregation columns without filtering the view
2.a) Overview
In many cases, you may want to filter a specific column within your view without affecting the entire view. For example, you might want to create a columns, "Sales by State[2022]" by including only the sales data for 2022 without filtering the view, so we can compare the sales of the year 2022 to the sales of the total sales.
KAWA provides a straightforward approach to achieve this selective column filtering without impacting the overall view.
2.b) Add and edit filters
In order to create the column "Sales by State[2022]": Click on the arrow icon in the header of the column to open Edit lookup Formula pop up
2.c) Synchronize fixed aggregation columns with the view filters
You can choose whether each view filter affects the fixed aggregation or not.
2.d) Recursive fixed aggregation
There is no limit to the recursion depth of fixed aggregation columns. You can apply fixed aggregation to columns that are already fixed, allowing you to create as many columns as needed.
3. INCLUDE aggregation
INCLUDE aggregation computes values using the specified dimensions in addition to whatever dimensions are in the view.
INCLUDE aggregation can be useful when you want to calculate at a fine level in the database and then re-aggregate and show at a coarser level in your view. Fields based on INCLUDE aggregation will change as you add or remove dimensions from the view.
4. EXCLUDE aggregation
EXCLUDE aggregation removes some dimensions from the view groupings for the purposes of the calculation, such as looking at the average blood pressure for patients on a certain medication without considering individual patients, even if the view is grouped by patient.
5. GROUPING features
5.a) GROUP aggregation
GROUP aggregations enable to compute formulas based on values at the group level.
Ex: Hit ratio by client : COUNT(done) / COUNT(done + missed)
5.b) GROUP ‘OTHER’
You can use the “Other” grouping in any view, which is particularly useful for scenarios like displaying the top 10 clients while grouping all other clients under a single "Other" category.