Question

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

• 0 replies
• 236 views

• New Member
• 0 replies

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  ANDis_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.

This topic has been closed for comments