Creating an automated generated max month field in a straight table.

[justcotto]

Hey,

I want to create a straight table that shows total counts by month of the top 30 suppliers and I want them ranked by the total of all months shown.  I want the month fields to be automated, so when a new month comes a new field is generated.  I have already created the variables for the year start for the max year.  I just want to know if I can create that in a straight table.

Example:

Supplier                                                                                   Jan         Feb                      Grand Total

ATHENA PATIENT REFUND	8,822	8,616		17,438
PT REFUND - DOMESTIC(NON-FOREIGN)	5,308	4,328		9,636
NEXTGEN CENTRA CARE	1,740	2,011		3,751
AHO SUNPORT PATIENTS REFUND	1,000	459		1,459
SPECTRUM BUSINESS	416	281		697
REIMBURSEMENTS	145	131		276
NEXTGEN ACC	156	107		263
HEART OF FL PATIENT REFUND	125	81		206
FERGUSON ENTERPRISES INC	173	32		205
EPIC PATIENT REFUNDS	129	59		188
CITY OF ORLANDO	42	79		121
ALSCO INC	92	24		116
AIRGAS USA LLC	59	54		113
GRAYBAR ELECTRIC CO INC	50	40		90

[dplr-rn]

if you want it in a striaght table you will need to use pick and match with an dynamic value list.

Easier maybe to just put it on a pivot table and move month to header

[justcotto]

So I did make a pivot and moved the month to header. I then used the rank function to rank the top 30 suppliers in a calculated dimension.

=Aggr(If(Rank(Count({1<Date={"<=$(vMaxMonYear)>=$(vYearStart)"}, METRIC_WID={'PMT'}>}INTEGRATION_ID))<=30, Supplier), Supplier)

But when I select a previous year or month it removes some suppliers from the top 30.  If that supplier had no counts in that month/year it will drop from the list.  If I could somehow lock this expression from allowing field selections to change the ranking.

[justcotto · Accepted solution]

I figured it out. I had to swap the final Aggr and If statement.

Works:

=If(Aggr(Rank(Count({1<Date={"<=$(vMaxMonYear)>=$(vYearStart)"}, METRIC_WID={'PMT'}>}INTEGRATION_ID)), Supplier)<=30, Supplier)

Does not work: 

=Aggr(If(Rank(Count({1<Date={"<=$(vMaxMonYear)>=$(vYearStart)"}, METRIC_WID={'PMT'}>}INTEGRATION_ID))<=30, Supplier), Supplier)

[justcotto]

is there anyway to add a total field at the end? to sum up each row?