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



Best Answers

  • 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 

    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.

Answers

  • 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

     

  • 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
     

  • Use QUARTERVALUE function 

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

  • 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.