2 Replies Latest reply on Jan 10, 2019 8:24 AM by Wil Paulk

    Calculation based on Date not in Data Source

    Wil Paulk
    Wil Paulk Jan 10, 2019 7:46 AM

      EDIT: removed (and (deactivation_date = 'March 2018') from query. While that is a piece of the puzzle, I am unable to use deactivation date as an anchor, considering I need to do other calculations using a similar formula without using deactivation_date. An example would be calculating all active users that have have spent prior to a month for each month.

       

      Hello all,

       

      I am attempting to create a Month over Month calculation based on a date that is not in the data.

       

      For example, the calculation for only the month of 'March 2018' would be similar too:

      IF (MIN(spend_date) < 'March 2018')

      then customer_number

      else null

      end

      * The dates in bold are the date that I would like to base this calculation off of MoM, but does not exist in my data.

       

      I need this calculation automated and for every month. Any help would be greatly appreciated!

       

      I would like the output to be:

      DateNumber of Customers that Fit Criteria:
      March 2018122 (This being the number of customers that had spend prior to March)
      April 2018141
      May 2018102

       

       

      Data source is as follows:

      customer_number
      		min(spend_date)
      		deactivation_datecustomer_status
      1234'2018-02-02 00:00:00.00''2018-03-13 00:00:00.00'deactivated
      2345'2018-04-23 00:00:00.00'nullactive
      3456'2018-06-30 00:00:00.00'nullactive
      4567'2018-01-04 00:00:00.00''2018-07-01 00:00:00.00'deactivated