Accessing the corresponding column value of each row in custom column

Get Help
1

I am in a situation where I have to change SQL to a GUI query builder for some reason.

I have four buckets (Below One M, Between One M Three M, Between Three M and Five M and Over 5M); inside each of the theses, I have 4 groups of rank (1, 2, 3 and 4) and I have a Count column which counted the number of times when each company were on that rank. The GUI query should show the percent of the time when the company was on the rank
Example: For each of the buckets, it calculates 20% of the time was in rank 1, 40% of the time was in rank 2, 10 % of the time was in rank 3 and 30 % in rank 4.

So my problem is I need to access the Count of each rank to calculate the percentage. I used the below line of code to have access to the corresponding value of Count for each rank. And by this, I have to add 16 new columns in the custom column to do this:

2
21102×612 47 KB

Then in the custom expression, I used them and ended up with a new 16 percent calculation like this
PercentRank1(5M)= SumIf([count], [Count Rank1-5M] = "Count Rank1-5M") / SumIf([count], [bucket] = "Over Five M")

3
31012×1131 27.1 KB

This is very inefficient, and it gets the visualization too messy. So can you help me with this to end up with the same result that I have in the SQL, like the below table with 4 columns, not 16 columns?

2

Hi @aramit
It would probably be easier if you showed your SQL.
Why can't you use SQL?

4

@aramit Such query would always be quite advanced in the GUI, and parts of it cannot be done currently in the GUI.
You should keep it as a SQL. You have not answered why you cannot use SQL.

5

to be used by non technical user probably I am not sure what is the reason it is what I have been told. so can you give me some insight to have access the count of each rank in the table ? or the way that I have used is the only way?

6

@aramit You'll have to create multiple questions to make something in the GUI that would be just as complicated as what you created.
You are using functions in SQL that the GUI is currently not able to do, which is why SQL exists as an option for those cases, so use SQL.

7

Thanks @flamber !