Yearly Insights with different accounting periods

[reinier.mostert]

Hi,

I checked the forums for information on changing the standard years for the Yearly insights solutions, but did not find any related info.

The Yearly Insights addon seems to be useful, and shows me the information that I need, but it is unfortunately (for me) based on calendar years. I have to report to the South African Revenue Services based on a financial year that runs from 1 March to 28 Feb. Is there a way for me to change the definition of the years used in the Yearly Insights addon, or even to select a custom period?

I’m quite comfortable with changing spreadsheets, but don’t know the Tiller setup too well.

Thanks in advance
Reinier Mostert

[KyleT]

Hi @reinier.mostert - I have an idea you can try if you like. In cell D3, you’ll put the start month of your financial year. For example, you would enter 3 for March as a starting month. And then you’ll adjust the two formulas in T1 and H2 so that they consider your start month in addition to the entered year. By default those formulas are only considering the year of the date value for each transaction in the transactions sheet.

As an implementation tip, I would suggest entering a 1 for the start month at first and notice that when you modify the formulas, nothing should change in the output. You should have the same results, because 1 is the start month of a regular calendar year.

Then for your financial year, if you enter year 2024 and update the start month to 3, your year would run from 1-Mar-2024 to 28-Feb-2025.

The new formula for T1 is:

=iferror(query(Transactions!A:AB,"SELECT "&O2&",sum("&O3&") WHERE "&O2&" IS NOT NULL AND ((YEAR("&O5&") = "&C3&" and MONTH("&O5&") >= "&D3-1&") or (YEAR("&O5&") = "&C3+1&" and MONTH("&O5&") < "&D3-1&")) GROUP BY "&O2&" ORDER BY "&O2&" LABEL sum("&O3&") 'Total'"),{"Category","Total"})

The new formula for H2 is:

=iferror(query(Transactions!A:AB,"SELECT "&O4&",sum("&O3&") WHERE "&O2&" IS NOT NULL AND ((YEAR("&O5&") = "&C3&" and MONTH("&O5&") >= "&D3-1&") or (YEAR("&O5&") = "&C3+1&" and MONTH("&O5&") < "&D3-1&")) "&if(LEN(O7),"AND "&O7,"")&" GROUP BY "&O4&" ORDER BY sum("&O3&") LIMIT "&C5&" LABEL sum("&O3&") 'Total'"),{"Description","Total"})

The substitution in each formula is the same - just adding additional checks against the month.

Check your data carefully to see that it behaves as expected. I did not do much data validation, but the logic simply looks at the year and month of each transaction and determines if it (1) equals the selected year and month greater than or equal to the start month OR (2) equals the next year and month less than the start month.

[reinier.mostert]

That does the trick! Thanks a lot, it is much more useful to me using the financial date!

I had to make a small change - the second formula had to go in H2, and not H1, but then it worked correctly.

Thanks again
Reinier