Best way to replace table calculations for month over month retention/results

[patrickstudent]

I need to track 3 data points month over month that all have to do with the volume of partners on our delivery platform. Right now I’m doing this with a table Look that has one row for each partner and a pivot set up by calendar month so that if a partner delivered in that month, there’s a 1 in the ‘Active’ column for that month. Then I use table calcs to return a 1 or 0 for each of the 3 data points. Data point descriptions and their respective table calcs below:

New - a partner that starts delivering in a given month after not doing so for 30 days (so if a partner last delivered 6 months ago and ago started up again tomorrow, they would be counted as ‘New’)

if(is_null(pivot_offset(${active},-1))
  AND ${active} = 1,
  1,0
)

Retained - a partner that delivered in a given month AND the previous calendar month

if(
  pivot_offset(${active},-1)=1
  AND
  ${active}=1,
  1,0
)

Churned - a partner that did not deliver in a given month, but did deliver in the previous month

if(
  pivot_offset(${active},-1)=1
  AND
is_null(${active}),
  1,0
)

Then I just use a sum table calc to get the total each month in New, Retained, Churned. 

My question: what’s the best way to do these calculations inside a View file, instead of table calculations? Doing it via table calc serverely limits our visualization options, as well as makes it hard to drill into the data further. 

I have a derived table with a column for deliveryid, partnerid, and deliverydate. What I can’t seem to figure out is how to create dimensions/measures within the view file that let me show these 3 data points month over month. I used a combination of CASE/WHEN and DATEDIFF to get it working on a rolling 30 day basis, but I can’t figure how to make it work for calendar month over month. 

We’re using Amazon redshift for our DBMS if that helps.