2 Replies Latest reply on Jun 14, 2018 2:24 AM by David Irvine

    2nd Subscription identification

    David Irvine

      Overview:

      I am working to identify customers that have increased their subscription within a subscription period. I.E. there subscription has a number of license and half way through the year they increase the number of licenses available.

      Data:

      The data i am working with is essentially:

      Company Name | Sub Start Date | Sub End Date | £ Amount | £ Licenses |

      I also have the pivoted version of this data set available. The data is coming from a SQL server so there is some ability to manipulate the structure of the import.

      Problem:

      Unfortunately, there is not an increases field and so increases in subscriptions are just any subscriptions for the same client that occur during an existing subscription period.

      These subscriptions can start and end at any time in the year but are almost always 1 year in length.

      I am trying to find a way to identify the subscriptions that are increases vs the original subscription for a time period.

      Ideally this solution would be a field that i can then use in a number of further Calculations. I.E. Table Calculations Solutions wouldn't be suitable.

        • 1. Re: 2nd Subscription identification
          Deepak Rai

          write 2 Calculations

           

          [MIn License]

           

          {FIXED Customer,Year (Date):COUNT(IF({FIXED Customer,Year (Date):MIN(DATE)}=DATE) Then License END)})}

           

          [Max License]

           

          {FIXED Customer,Year (Date):COUNT(IF({FIXED Customer,Year (Date):MIN(DATE)}=DATE) Then License END)})}

           

          Comparison

           

          IF [Max License]>[MIn License] Then Customer Name End

          Thanks

          Deepak

          • 2. Re: 2nd Subscription identification
            David Irvine

            Hi Deepak,

             

            Thanks for coming back on this so quickly.

             

            Firstly your Min License and Max license calculations are exactly the same. I am assuming the Max calculation should be if the date = Max date for the year.

             

            Also can you explain the logic that you are anticipating these calculation handling.

             

            Given the above, I have the situation that the original subscriptions could start in June 2017 ending in June 2018. However the increase could start in February 2018. So we need to handle subscriptions starting in different years. Which i am not sure the above would handle sufficiently yet.

             

            Also i am trying to identify the subscriptions that are increases vs original for a company. Meaning i need to be able to differentiate between two invoices for the same company in the same year. As you calculations are all fixed to the Company, Year(Date). Then the output of the formula is going to be the same for all subscriptions happening in the same year. Which wouldn't differentiate as needed. The level of detail of the end calculation should return to the subscription level to achieve the desired result.