I would like to calculate a percent rank within a group (states in my case), but not on a summary function which I believe the percenrank function requires, just on a number field. Is there any way to do this in Quicksight? I haven’t tried the sql functionality, will that allow me to calculate a new field using a window function?
Or do I need to do this outside of quicksight like in excel or something and re-import my dataset?
Hi @nw2this, if you want to calculate percent rank within a group without using the built-in summary functions, you indeed might face some limitations. QuickSight’s calculated fields are quite powerful, but they don’t directly support SQL window functions which are typically used for this kind of calculation. However, there are a few ways you can approach this problem:
In Amazon QuickSight, if you want to calculate percent rank within a group without using the built-in summary functions, you indeed might face some limitations. QuickSight’s calculated fields are quite powerful, but they don’t directly support SQL window functions which are typically used for this kind of calculation. However, there are a few ways you can approach this problem:
If your dataset is in SPICE (Super-fast, Parallel, In-memory Calculation Engine), you can use the QuickSight’s SQL editor for datasets to apply SQL transformations including window functions. Here’s how you can use SQL to calculate percent rank within states:
SELECT *,
PERCENT_RANK() OVER (PARTITION BY state ORDER BY your_number_field) AS PercentRank
FROM your_table_name
This approach leverages QuickSight’s SQL capability to apply advanced analytics like window functions directly within QuickSight.
As you suggested, another method is to perform the calculation outside of QuickSight, such as in a database or in Excel, and then import the dataset with the percent rank already calculated. This method might be easier if you are comfortable with SQL or Excel and have access to these tools to preprocess your data.
If neither of the above options suits you and if your data set isn’t too large, you could potentially create a cumbersome workaround using QuickSight’s available functions, but this wouldn’t be straightforward or efficient as using SQL.
Each method has its trade-offs regarding ease of use, performance, and flexibility, so the choice depends on your specific needs and environment. If you haven’t used the SQL functionality in QuickSight yet, it’s worth exploring, especially for advanced data transformations like this.
We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)
Could you post a screen shot of what you click to get to the sql editor (step 2). I don’t see it. I am in the data prep/edit data area of a dataset that has been imported to spice. @Xclipse
Yes, I watched that video after you posted your solution and before asking my follow-up question. In that video he mentions all kinds of data sources, but not a csv that has been imported into SPICE. I don’t get that option when I import a csv or when I open my already existing SPICE dataset.
