Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
justcotto
Contributor III
Contributor III

Ranking top 30 in a pivot table for rolling 13 months

I have a table that holds supplier transaction data.  I want to create a table chart that ranks the top 30 suppliers count(transactions) for a rolling 13 month period. The chart should also be static to the {1}max date.  I have already created the variables for vMaxDate and vRolling13Months. I have created a pivot table and dragged the Month-Year dimension to the top.  But I want them to be ranked by the total count of the 13 Months.  Also for some reason this is not ranking the top 30 suppliers.

chart.PNG

My calculated dimension is : =If(Aggr(Rank(Count({1<Date={"<=$(vMaxMonYear)>=$(vRolling13Mon)"}, METRIC_WID={'PMT'}>}INTEGRATION_ID),3), Supplier)<=30, Supplier)

My other dimension is Month-Year

My expression is: =Count({1<Date={"<=$(vMaxMonYear)>=$(vRolling13Mon)"}, METRIC_WID={'PMT'}, [Payment Method]={'CHECK'}>}INTEGRATION_ID)

 

0 Replies