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,'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

      Pivot challenge fig 1.PNG

      Pivot challenge fig 2.PNG