Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
justcotto
Contributor III
Contributor III

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

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 REFUND8,8228,616 17,438
PT REFUND - DOMESTIC(NON-FOREIGN)5,3084,328 9,636
NEXTGEN CENTRA CARE1,7402,011 3,751
AHO SUNPORT PATIENTS REFUND1,000459 1,459
SPECTRUM BUSINESS416281 697
REIMBURSEMENTS145131 276
NEXTGEN ACC156107 263
HEART OF FL PATIENT REFUND12581 206
FERGUSON ENTERPRISES INC17332 205
EPIC PATIENT REFUNDS12959 188
CITY OF ORLANDO4279 121
ALSCO INC9224 116
AIRGAS USA LLC5954 113
GRAYBAR ELECTRIC CO INC5040 90
1 Solution

Accepted Solutions
justcotto
Contributor III
Contributor III
Author

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)

 

View solution in original post

4 Replies
dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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)

 

View solution in original post

justcotto
Contributor III
Contributor III
Author

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