Skip to main content
Solved

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

  • 3 November 2021
  • 4 replies
  • 459 views

pandachan48
Jr Varsity II
Forum|alt.badge.img

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?

Best answer by lauraj46

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

View original
Did this topic help you find an answer to your question?

4 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • Answer
  • November 4, 2021

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


pandachan48
Jr Varsity II
Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 31 replies
  • November 5, 2021

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?


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • November 5, 2021

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


pandachan48
Jr Varsity II
Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 31 replies
  • November 8, 2021

Hi @lauraj46,

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


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings