I am ranking employee based on their score and listing TOP 10. Now the ranking works perfectly. But one of the filter is getting applied after ranking and hence not showing all 10 employees.
FIlter condition: isOutlier equals 1
calculated field 1:isOutlier
ifelse({job_count} > maxOver({job_count}, [], PRE_AGG) * 25/100 , 1, 0)
//We need to consider only those employees for ranking who have worked for us at least a minimum number of jobs. Hence we are only considering drivers who worked more than 25 % of the max job count worked by an employee during the time period
calculated field 2: job_count
distinctCountOver({JOB ID},[{EMP ID}], PRE_AGG)
// job count per employee
When I disable this filter I can see all the TOP 10 rankings.
Pasting rank function also here:
denseRank([avgOver({SCORE}, [{EMP ID}], PRE_AGG) DESC], [], PRE_AGG)
I think it can be solved by updating the calculation level from PRE_AGG to POST_AGG_FILTER. As POST_AGG_FILTER is default option, you may simply remove PRE_AGG. I have created an example below. Please try if it can solve the problem
Step1: created calculated fields qty_sum_city to show the sales qty by city. created another calculated field qty_sum_city_top20 to indicate which cities are top 20 sales quantity. qty_sum_city ==> sumOver(Quantity,[City], PRE_AGG) qty_sum_city_top20 ==> ifelse({qty_sum_city} > maxOver({qty_sum_city}, [], PRE_AGG) * 20/100 , 1, 0)
Step2: Add a filter condition to show only top 20 city by sales qty only
Step3: Create another calculated field denseRank to rank the top 8 city by Sales Amount. The city was ranked by the list of city that I have filtered in step2 denseRank_sumOver_city: denseRank([sumOver(sum(Sales),[City]) DESC], [])
Hi @Philip, did Roy’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!
