How to display Rank / % of the group after filtering

Question & Answer
, ,
1

I have a pivot table like this - I would like for each A/B/C/D, to show only their rows of information.

image

Like below:

image

Filtering doesn’t help because then that makes rank : 1 and % to 100%.

image

What’s the best way to achieve this - basically working with filtered Data VS whole data without showing other data?

Thank you,

2

Hi Liz! You can accomplish this in QuickSight using level-aware calculations. The use case you describe is covered in our Author Workshop exercises on calculations. This link will take you directly to the calculation exercises in the Workshop. Scroll down to exercise 6 for this use case. Workshop Studio.

3

Thank you - I did try this prior to asking but I’ve been failing- do you think you can advise me with more detail how I can accomplish this? What function to use and how- I did try rank function with PRE_FILTER with different combinations but I was not able to accomplish my goal - either error or not the result I wanted.

Thanks!

4

I believe this is similar question by someone else- but no answer was posted.

Thanks,

5

Hi @Liz -

You can use LAC-W functions for this.

Here’s an example for some common use cases.

Step 1 - Create a calculated field for the YTD amount per vendor. This uses a LAC-W sumOver for partition year, and vendor.
c_vendor_ytd_amt

sumOver(amt,[truncDate('YYYY',dt),vendorname], PRE_FILTER)

Step 2 - Create a calculated field that assigns a row number across your percentilerank dimension
c_vendor_rn

// use rank function as row_number over vendor name
rank([dt DESC],[vendorname], PRE_FILTER)

Step 3 - Create a calculated field using the percentileRank LAC-W function. This does not have “Over” in the name but it is a LAC-W. The partition is the row number.

c_vendor_amt_percentilerank

percentileRank([{c_vendor_ytd_amt} ASC], [{c_vendor_rn}], PRE_FILTER)

Step 4 - This is an alt. calculation if you a symmetric percentile range (0-1 or what you see in Excel)

c_vendor_amt_percentilerank_symmetric

100*(rank([{c_vendor_ytd_amt} ASC],[{c_vendor_rn}],PRE_FILTER)-1)/(distinctCountOver(vendorname,[],PRE_FILTER)-1)

Step 5 - Filter your visual to only include 1 row per dimension.
image

If you are using the symmetric calc you can put this in c_vendor_ytd_amt with no filter needed.

ifelse({c_vendor_rn}=1,sumOver(amt,[truncDate('YYYY',dt),vendorname], PRE_FILTER),null)

Step 6 - Create a calculated field to show the vendor rank
c_vendor_ytd_amt_rank

rank([{c_vendor_ytd_amt} DESC],[{c_vendor_rn}],PRE_FILTER)

Step 7 - Create calculated field for the grand total of all vendors
c_vendors_ytd_total

sumOver(amt,[truncDate('YYYY',dt)], PRE_FILTER)

Step 8 - Create a calculated field for your percent of total
c_vendor_percent_of_ytd_total

{c_vendor_ytd_amt}/{c_vendors_ytd_total}

Result:

image
image1146×809 60.6 KB

1 Like
6

Thank you so much for such a thorough instruction for me- lot more complicated than I thought- I will follow this through and update!

7

I forgot to update it. Today I saw similar question referring to my question - thought I should put in what worked for me in the end.

The solution itself didn’t work as is exactly but yes it totally guided me to the solution I needed.
I used : (I changed my filter to have from date and to date for more flexibility as requested by users)

V_Sum_TY : sumOver(ifelse(truncDate(“DD”,ppdate)>=${datefr} and truncDate(“DD”,ppdate)<=${dateto},total,0),[vendorid],PRE_FILTER)

V_Rank_TY : denseRank([{V_Sum_TY} DESC],,PRE_FILTER)