Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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