0 Replies Latest reply: Jan 17, 2016 8:28 AM by Simon Watson RSS

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

    Simon Watson

      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,count({<NumberOfIssuesFulfilled=>}Subscriptions)+aggr (count( {<NumberOfIssuesFulfilled={">12"}>} Subscriptions),Publication_Month),



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

      Has anyone got any ideas?




      Pivot challenge fig 1.PNG

      Pivot challenge fig 2.PNG