I have a field called as Program, and another field metric count.
I want to create a field called as Rank of program based on metric count.
Created a calculated field in quicksight using:
rank
(
[sum({Metric count}) DESC],
[Program]
)
But , when i am trying to plot two these type of fields in scatter plots, getting this error :
We can't parse this SQL syntax. If you are using custom SQL, verify the syntax and try again. Otherwise, contact support.
Hello @Renuka_Munjal, welcome to the QuickSight Community! Maybe if you tried using the denseRank function instead it would work with the visual. What else are you putting in the field wells for your visual?
Modified calculated fields , by making use of denseRank, still same.
ProgramRankUsage
denseRank
(
[sum({Metric count}) DESC],
[Program]
)
ProgramRankCost
denseRank
(
[sum({Actual Cost}) ASC],
[Program]
)
Not giving SQl error now, but giving same ranks for all programs.
Basically what i want to do is to create two types of ranks:
But now i am getting rank 1 for all the programs.
What i want is for eg. ProgramRankUsage, i want that for all the similar programs , metric count gets summed, and accordingly the program with max metric count sum gets rank 1, and so on.
And i want unique ranks for all the programs.
Hi @Renuka_Munjal - Welcome to AWS QuickSight community and thanks for posting this interesting question. Is it possible to share sample data and expected output. This will help community member to replicate and guide you the solution.
Tagging @DylanM @David_Wong @sagmukhe for their advise as well.
Regards - Sanjeeb
Hi @Renuka_Munjal,
Before you create a scatter plot, I suggest putting your calculated fields in a table visual first. This will help us figure out if the issue is with the particular visual type that you chose or with the calculated fields themselves.
What happens if you put the calculated fields with the rank function in a table visual?
Hi @David_Wong ,
Here,
put into table visual, still Rank coming as 0 for all.
There are multiple rows for a single program, what i have done is created a calculated field SumofMetricCount, to sum metric count for all unique programs, now i want rank of programs based on SumofMetricCount, basically for eg, here output should have been something like this :
Program SumOfMetricCount ProgramRankUsage
1p_2p 1 3
3p 3 2
wifikdm 45 1
Hi @Sanjeeb2022 ,
There are multiple rows for a single program, what i have done is created a calculated field SumofMetricCount, to sum metric count for all unique programs, now i want rank of programs based on SumofMetricCount, basically for eg, here output should have been something like this :
Program SumOfMetricCount ProgramRankUsage
1p_2p 1 3
3p 3 2
wifikdm 45 1
Can you try adding PRE_AGG to your calculated field?
rank
(
[sum({Metric count}) DESC],
[Program],
PRE_AGG
)
If i am doing this :
rank
(
[sum({Metric count}) DESC],
[Program],
PRE_AGG
)
I am getting error : For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.
Tried this too :
rank
(
[{SumOfMetricCount} DESC],
[Program],
PRE_AGG
)
For this getting : LAC aggregation functions cannot be used as operands of LAC window functions.
Nvm, I know what’s your with your calculated field. You need to use your entire dataset instead of Program as your partition, i.e. don’t put anything inside the square brackets.
rank
(
[sum({Metric count}) DESC],
[]
)
great, this worked.
Thanks.
One thing, is there any way to assign unique ranks to each program ?
Do you mean if there is a tie? The only way I can think of is to write a custom SQL query and use the ROW_NUMBER function at the dataset level.
Basically what my real use case is, I want to divide programs into 4 types of categories :
I want to divide programs into 4 categories in Scatter plots:
based on these two ranks ProgramRankUsage and ProgramRankCost
Putting Program as Label,
was thinking to a Cost-Usage Category as calculated field in Color, so that program with unique ranks can be divided into 4 categories.
But if ranks would be same, not sure how to do that.
Hello @Renuka_Munjal, would you mind posting your last comment as a new question in the QuickSight community? That way you will be at the top of the priority list for a response from one of our QuickSight experts and it can be searched separately from your original post in the community. Feel free to attach a link to this post to give context to your question. Thank you!
