Quicksight Level Aware

Question & Answer
1

Hello, I am looking for something like ‘Exclude’ in a table calculation function.

Currently, I have an ID filter and I am something like this

And there is an ID filter:

–salary is already an aggregate . Think of salary as sum( x)/sum(y)
rank([{salary} DESC],[{country}])
or
dense rank([{salary} DESC],[{country}])

The above is working when I select all values and the ranking is showing correctly But when I select 1 ID, then the ranking does not work as it is ranked after the filter is applied. thanks

2

I ended up doing it in sql to pass as a dimension

3

@sirish_bhatta -

If you revisit this within QuickSight, you can use the LAC-W version of Rank.

Rank does not have the usual rankOver naming but it is a level-aware calculation.

You can do this, which will ignore your Analysis filters.

rank([{salary} DESC],[{country}], PRE_FILTER)
4

Thank you. Salary in this example is already aggregated and Quicksight doesn’t let you use pre-filter

5

Hi @sirish_bhatta -

You can nest the LAC-W functions.

inner_cf
maxOver(sumOver({x},[{country}], PRE_FILTER)/sumOver({y},[{country}], PRE_FILTER),[country],PRE_FILTER)

rank
denserank([{inner_cf} DESC],[], PRE_FILTER)

image
image1151×253 6.06 KB

6

thank you. i need to test it out. currently i have implemented a workaround through the query

7

Hi, @sirish_bhatta. Did @robdhondt’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!