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?
+8
Hi
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
Hi
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?
+8
Hi
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
Hi
yes, this is what I’m looking for. thank you!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.