Overview
For details about hierarchical coordinates, see Learning Path of Hierarchical Coordinate. With this learning schedule, you can learn hierarchical coordinates from the basics to more advanced levels.
Application Scenario
The conditional summary is used to count the number of data that meets certain conditions.
For example, the number of months with the amount payable exceeding 2500 in each year is calculated, as shown in the following figure.
Implementation Method
You can use the COUNT() function together with the conditional expression of hierarchical coordinates.
Example
Data Preparation
Create a dataset named ds1. The SQL statement is:
SELECT STRFTIME('%Y',Order Date) AS Year, STRFTIME('%m',Order Date) AS Month, Amount Payable
FROM Order
WHERE STRFTIME('%Y',Order Date) IN ('2010','2011').
Table Design
Design the table as shown in the following figure. Drag the fields from ds1 to cells A2, B2, and C2. Cell C3 is used for calculating the conditional summary and counting the number of months with the amount payable exceeding 2500 in each year.
The conditional summary formula for C3 is COUNT(C2[!0]{A2=$A2&&C2>2500}). The following table explains the formula in detail.
| Formula | Description |
|---|---|
COUNT() | Counts the number of all data in the parentheses. |
C2[!0]{} | Outputs all the data that meets the conditions in the curly braces. |
A2=$A2 | It is a part of the conditional expression, indicating that the calculation is limited within each group. That is to say, the data of each year is calculated separately. |
C2>2500 | It is a part of the conditional expression, indicating that the amount payable needs to exceed 2,500. |
Effect Display
Save the report and click Pagination Preview. The following figure shows the preview effect.
The report can be previewed on the DataAnalyst APP and HTML5 terminal, as shown in the following figure.
Template Download
For details, you can click to download the template: Conditional Summary.cpt.
Notes
The conditional summary is not supported when you extract data from multiple sheets or multiple table components.
Because if the formula contains conditional expressions with {} (curly braces), the formula will be invalid when you extract data from multiple sheets or multiple table components.
