Set analysis, aggr in pivot. Only works when a selection is made

[#1]

Hi community,

The challenge I have is that I want to get a pivot table for number of issues fulfilled against the publication month. The data shows the number of customers who have lapsed, I would like to show this as the total remaining, e.g. total - lapsed.

I have been working on a single publication month, and the data is perfect. When I then expand it to the full data set (multiple publication months).

What I'd like to achieve is:

1. If issues are greater than 12 (a year), I would like to see a zero.
2. Where 12 issues are received, I would like to see the count for 12+.
3. For all others, I would like to see Total subscriptions minus the subscriptions for that number of issues fulfilled

The formula I have is:

=if(NumberOfIssuesFulfilled>12,'0',

if(NumberOfIssuesFulfilled=12,count({<NumberOfIssuesFulfilled=>}Subscriptions)+aggr (count( {<NumberOfIssuesFulfilled={">12"}>} Subscriptions),Publication_Month),

$(=Aggr(count(Subscriptions),Publication_Month))-rangesum(above(count(Subscriptions),0,12))))

The logic works for one month, but when the data set is expanded, it all falls apart.

Has anyone got any ideas?

Thanks.

Simon