Question

So I used this formula to give me the amount of money spent this month, and used relation and rollup to display it in another database with totals.

if(formatDate(prop("Date"), "MM YYYY") == formatDate(now(), "MM YYYY"), prop("Amount"), toNumber(""))

Now I need to do the same thing except for the last 3 months, I tried to do similar formula with datesubtract but cant get it to work

Answer 1

Hi,

I have put something together. Let's see if it works for you:

if(prop("Date") >= dateSubtract(now(), 3, "months") and prop("Date") < now(), prop("Amount"), toNumber(""))

It checks whether the "Date" property of the transaction is within the last 3 months from the current date, without simplifying the dates to their month and year. It should output the "Amount" for transactions made within the last 3 months and an empty number for older transactions.

The only think is that the dateSubtract function doesn't account for differences in the lengths of months. Therefore, "3 months ago" from a date in March will be considered as a date in December of the previous year, regardless of whether it's late or early March. This is usually not an issue, but could be relevant for very specific needs.

Let me know if it is suitable for you. If not I can always adjust it.

Martin