Quarterly Totals in Months

tyagi_shub
Occasional Contributor

Quarterly Totals in Months

Community,

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!

Thanks,
Shubham

P:S: turning on quarterly totals or adding/mapping another dimension say "Quarters" list is not an option



2 ACCEPTED SOLUTIONS

Accepted Solutions
vinayvm
Certified Master Anaplanner

Re: Quarterly Totals in Months

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.

 

Data View

Screenshot 2021-05-24 at 12.32.53 PM.png

 

Formula View

Screenshot 2021-05-24 at 12.33.07 PM.png

 

Hope this helps!

 

Regards,

vinayvm

View solution in original post

ChrisAHeathcote
Community Boss

Re: Quarterly Totals in Months

@tyagi_shub 

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.

Chris
HeathcoteAndHerran.com

View solution in original post

7 REPLIES 7
ArunManickam
Master Anaplanner/Community Boss

Re: Quarterly Totals in Months

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.

 

Thanks

Arun

 

 

 

 

 

Thanks

Arun

 

tyagi_shub
Occasional Contributor

Re: Quarterly Totals in Months

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.

Thanks,
Shubham
 

nara.lokesh486
Occasional Contributor

Re: Quarterly Totals in Months

Use QUARTERVALUE function 

vinayvm
Certified Master Anaplanner

Re: Quarterly Totals in Months

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.

 

Data View

Screenshot 2021-05-24 at 12.32.53 PM.png

 

Formula View

Screenshot 2021-05-24 at 12.33.07 PM.png

 

Hope this helps!

 

Regards,

vinayvm

tyagi_shub
Occasional Contributor

Re: Quarterly Totals in Months

Thanks, @vinayvm, 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.

ChrisAHeathcote
Community Boss

Re: Quarterly Totals in Months

@tyagi_shub 

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.

Chris
HeathcoteAndHerran.com
tyagi_shub
Occasional Contributor

Re: Quarterly Totals in Months

Thanks @ChrisAHeathcote, my logic parameters were wrong but now it's all good and working.