Quarterly Totals in Months
In my module for forecasting salaries, I want quarterly totals in months (see attached) to be used for Superannuation calc. But I want to achieve it using MOVINGSUM (or similar) based on a "Pay" line item which resets after every 3 months.
Any help would be greatly appreciated!
P:S: turning on quarterly totals or adding/mapping another dimension say "Quarters" list is not an option
Hi @tyagi_shub ,
You can achieve it from using the functions MOD and offset (however, there could be any other methods as well).
I have attached two screenshots which you can refer.
Hope this helps!
If you would like to use MOVINGSUM then utilise the 'Pay' line item to feed the function.
Example - MOVINGSUM(Salary Per Month, 1 - Pay, 3 - Pay)
This should correctly calculate the 'Salary Per Quarter' line item and is by far the simplest solution.2
Hello @tyagi_shub ,
Create a line item called Qtr_total and the formula should be
salary[lookup:SYS Time Months.quarter]
SYS Time Months module is your time settings module by Month, where you have a mapping between your months and quarter. Hope you can create a formula for finding the quarter of the month.
Let me know if you need help.
Hi @ArunManickam ,
Thanks for the reply.
But how can you identify quarter of the month when your quarterly totals are off and you cannot select "Quarters" as a format in a line item? and that is why I've mentioned in my post script that turning on "Quarterly totals" is not an option for this one.
Use QUARTERVALUE function0
Thanks @ChrisAHeathcote, my logic parameters were wrong but now it's all good and working.0
Thanks, @Vinay VaradarajM, this is indeed another good solution which I will be using If I don't have to use the MOVINGSUM along with my "Pay" line item. But still, thank you, this is good learning for me.0