Data manipulation & Modify payment frequency

Question

Hi am trying to manipulate the following dataset to have the layout in the excel table below. I attached a sample data as well that could be used in a workflow. Thanks a bunch and would appreciate any help.

The dataset is membership IDs and payment frequency. So 1=annual 2=semi-annual, 3=quarterly, 4=annual. Members sometime change their payment but usually not more than once. I am trying to spread the payments over the course of the year for each member based on the payment frequency but need to account for changes in payment frequency.

In addition, i need some help in trying to think about how to write the formula to choose the correct payment frequency based on when the change occurred. For example, right now i only have 1 payment frequency type but need the payment frequency description to be dynamic (choose based on the payment date). For example, if a member made a payment on 03/17/2019 and they changed from a semi-annual to monthly payment on 03/17/2019 i want the make sure i am now spreading the payment on a monthly basis. I have not encountered instances where the payment frequency changes more than once. Other fields that i have that could be used are "last payment date", "next payment date", "payment date".

Current formula.

if [PaymentFrequency_Description]="Annual" THEN DateTimeAdd(DateTimeTrim([start date],"lastofmonth"),11,"months") ELSEIF [PaymentFrequency_Description]="Semi-Annual" THEN DateTimeAdd(DateTimeTrim([start date],"lastofmonth"),5,"months") ELSEIF [PaymentFrequency_Description]="Quarterly" THEN DateTimeAdd(DateTimeTrim([start date],"lastofmonth"),2,"months") ELSEIF [PaymentFrequency_Description]="Monthly" THEN DateTimeAdd(DateTimeTrim([start date],"lastofmonth"),0,"months") ELSE [start date] ENDIF

Sample data.xlsx

MGoldstein · Answer

Thanks -  the formula we wrote already cover this by removing the date and making it the last of month. Was more so looking for tangible suggestions to modify the formula to be dynamic in addressing the data. There are additional layers of complexity such as members pausing membership, being marked as deceased (need to stop payments at a certain date), members not paying one month and then paying double the subsequent month on the same invoice so when we try to map the payment to the invoice the prior months invoice still shows zero so the spread is off. Really need some sort of complex nested if formula that considers many scenarios in subscription membership revenue models which is kind of what we were looking for/if there was anything out there.

Thanks again.

SeanAdams · Answer

Hey @MGoldstein

Depending on your business - payment frequencies like this can be simple or super complex.

For example -

Complex: if I joined in Jan; and paid monthly in Jan & Feb - then changed to semi-annual - then I would have to pay for Mar; Apr; May; June in my June payment.

- in other words - it's semi-annual based on joining date; and makes the client whole for that period

Simple: If I joined in Jan; and paid monthly in Jan & Feb - then changed to semi-annual - then my next payment is in 6 months (Aug) for the intervening 6 months.

- in other words, it's semi-annual based on last payment date.

If it's the simple option, then just use a lookup table to add on the right number of months.

Trick for dealing with dates - if you strip off the day portion you can manipulate months more easily (it takes care of the different month lengths

For example: 17 Sept 2020.

- to get the first day of next month - just strip off the 17 and add on 01.   Now you're left with 01 Sept.    Then add 1 month.

- To get the last day of the month - just strip off the 17 and add on 01.   Now you're left with 01 Sept.    Then add 1 month and subtract a day.

MGoldstein · Answer

I was able to do the manipulation with transpose and cross tabs.

If anyone has thoughts around the second part, please let me know. Thanks!