All Products
Search

This Product

    Quick BI:Percentile

    Document Center

    Quick BI:Percentile

    Last Updated:May 22, 2024

    Percentile calculation is used to rank the results returned from the database in the form of percentiles, so that users can observe the position of a data in the whole group of data. This topic describes how to configure a percentile.

    Prerequisites

    Limits

    • Charts that support percentiles are:

      • Line /surface chart: line chart, area chart chart, stacked area chart area chart, percentage stacked area chart, combination chart.

      • Bar charts: column chart, stacked column chart, 100% stacked column chart stacked column chart, circular bar chart, leaderboard, bar chart, stacked bar chart, 100% stacked bar chart stacked bar chart, dynamic bar chart, and waterfall chart.

      • Bubble /Scatter Chart: bubble chart, scatter chart, and facet scatter chart.

      • Table type charts: cross tables and details tables.

      • Pie /ring charts: pie charts, polar area chart charts, radar charts, and rectangle hierarchy chart.

      • Geography charts: colored map, geo bubble chart, heat map, flying line map, and symbol map.

      • Indicator charts: kanban and indicator trend charts.

      • Funnel /conversion relationship charts: funnel chart and comparison funnel chart.

      • Other charts: word cloud.

    • This analysis method is calculated based on the returned result set. Currently, the upper limit of the public cloud is 10000. If the total amount of data returned exceeds 10000, the calculation result may not meet expectations. Please pay attention!

    Background information

    Percentiles are another way to indicate rankings. First, you need to sort a set of data and then calculate it according to the following formula:

    image

    where imagerepresents the calculated percentile, imagerepresents the total number of this set of data, and imagerepresents the rank of the data point.

    For example, the percentile calculation result for a set of data is as follows:

    Raw data

    20

    10

    30

    40

    50

    20

    Ranking in ascending order

    3

    1

    4

    5

    6

    3

    Ascending Percentile

    40%

    0%

    60%

    80%

    100%

    40%

    Descending Rank

    5

    6

    3

    2

    1

    5

    Descending Percentile

    80%

    100%

    40%

    20%

    0%

    80%

    Note

    The same data exists when using sparse parallel ordering: 1,3, 3,4, non-consecutive parallel ordering: 1,2,2,4.

    Usage notes

    • Totals and subtotals do not participate in percentile calculation and cannot coexist with percentile calculation. That is, if percentile calculation is selected, totals and subtotals are automatically invalid (displayed as "-").

    • The calculation of empty values is skipped, and the final result of the cell is still empty (displayed as "-").

    • The percentile calculation is based on the result set returned by the database, so it only takes effect within the order of magnitude allowed by the fetching limit, which is 1w for the public cloud.

    How to configure the global resource plan

    You can follow the steps shown in the figure to configure the advanced computing percentile. Percentile allows you to set Ascending, Descending, Within-Group Ascending, and Within-Group Descending.

    image.png

    Example

    The descending order in the group is used as an example.

    1. After you log on to the Quick BI console.

    2. Follow the steps shown in the following figure to go to the dashboard editing page. image.png

    3. In the top navigation bar, click Add Chart. Find the imageicon and create a cross table.

    4. On the Fields tab of the Chart Design page, configure the cross table data.

      The target dataset is company_sales_record.

      1. In the Measures list and Dimensions list, find the required fields and double-click or drag them to specific fields.

        • In the Dimensions list, find Area, Province, and City, and double-click or drag them to the Rows area.

        • In the Measures list, find order_amt and double-click or drag it twice to the Columns area.

          Note

          Two order amount fields on the Columns section, one displays the raw data and the other sets the percentile.

          If two duplicate fields are added to a measure, the message "Duplicate fields exist in the measure" appears. In this case, you do not need to update the data. You can update the data after the advanced computing percentile is configured.

      2. Find the second order amount in the Columns section, click the imageicon on the right, and choose Advanced Calculation-> Percentile-> Descending in Group. image.png

      3. Click Update.

        In this case, the cross table is grouped by province, and then the result of the order amount in each group is calculated separately in descending order of percentile.

        image.png

      Actual Calculation Method

      • Ascending order: The results of all returned order amounts are calculated in ascending order.

      • Descending order: The descending percentile is calculated for the results of all returned order amounts.

      • Within-Group Ascending: Groups are sorted by province, and then the result of the order amount in each group is calculated in ascending order.

      • Within-group descending order: groups are performed by province, and then the order amount in each group is calculated in descending order.