Solved

How to display YOY sales in a pivot table using Financial Year

  • 3 November 2021
  • 4 replies
  • 38 views

Userlevel 1
Badge

Currently the date that I’m using is the sales order date(according to the calendar date). However, I want it to display according to our Financial Year (1apr-31mar), whereby it shows the total of each Financial Year. 

I’m thinking of adding a column for financial year in the Sales Order generic inquiry, but would require a formula to calculate the financial year from the sales order date. Any idea how to do it?

icon

Best answer by lauraj46 4 November 2021, 20:22

View original

4 replies

Userlevel 3
Badge +1

Hi @pandachan48 ,

If you know in that your fiscal year is always April 1 - March 31st, then you could write a formula to adjust the date by 3 months, something like this:

=Year(DateAdd([SOOrder.OrderDate], 'm', -3))

Hope that helps!

Laura

Userlevel 1
Badge

Hi @lauraj46,

yes, it works! Thank you so much! 

In the pivot table (as shown in the pic), the column I’m still using the Sales Order Date, thus the values are not able to display on top of each other.

This makes it hard to do further analysis as I would like to easily compare last year’s quarter/month to this year’s same quarter/month. Instead of having the formula to get the fiscal year only, is there a formula to get MM-YYYY and YYYY-Q?

Userlevel 3
Badge +1

Hi @pandachan48 ,

I don’t think the generic inquiry supports a general date format function, but you can build your own pretty easily.  Take a look at these examples and let me know if that does what you are looking for.

 

MM-YYYY  

=right('0' + CStr(Month(DateAdd([SOOrder.OrderDate], 'm', -3))), 2) + '-' + CStr(Year(DateAdd([SOOrder.OrderDate], 'm', -3)))

 

YYYY Q

=CStr(Year(DateAdd([SOOrder.OrderDate], 'm', -3))) + ' Q' + CStr(Floor(Month(DateAdd([SOOrder.OrderDate], 'm', -3))/4)+1)

 

Laura

Userlevel 1
Badge

Hi @lauraj46,

yes, this is what I’m looking for. thank you!:grin:

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved