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

# Calculation based on Date not in Data Source

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
• ###### 1. Re: Calculation based on Date not in Data Source

A lot of what you're trying to do will depend on what your data looks like (and the density of values, and the data types).

Can you upload a packaged workbook?

If you are concerned about proprietary data, check out the video here:

I can tell you this:  You can't create marks that are not somehow present in your data.  Especially dimensional values.

You COULD create 12 separate calcs, one for each of the prior 12 months based on the value of TODAY().  And then use MeasureNames/MeasureValues to display them.  Again, a packaged workbook would help me walk you through that.

• ###### 2. Re: Calculation based on Date not in Data Source

Hi Joe,

I am unable to upload the workbook at present, but as soon as I have the opportunity I will upload. I appreciate your help!