Sage X3

Welcome to the Sage X3 Support Group on Community Hub! Available 24/7, the Forums are a great place to ask and answer product questions, as well as share tips and tricks with Sage peers, partners, and pros from around the globe.

Fiscal Year instead of Calendar Year in reports

Posted By Katrina Chaney 3 months ago

We are trying to create sales reports using a Fiscal Year. I can use the between-date feature to give me the actual dates (Dec - Nov). However, the periods follow the calendar year and not our fiscal year. How do I make it utilize fiscal year instead?

Also if there are no sales in a particular month it just doesn't show that period is there a way to make it have zeros for the customers that month to show no sales?

Thanks in advance,

Katrina

0 Shai TSEMAH 3 months ago

Not sure I understand your 2nd paragraph request , however I've tackled the subject of Fiscal year with adding a calculated column set as type Dimension data on your data model of choice (either based on SORDERx tables or OLAP cube)

For example - our fiscal is between October and September

Here's the argument for TSQL ,but it should work with other SQL revisions

CASE WHEN MONTH(<Your order Date field>) BETWEEN 1 AND 9 THEN YEAR(<Your order Date field>) ELSE YEAR(<Your order Date field>)+1 END

So assuming you want to display and/or filter Fiscal year 2024 , it will show all orders in which CREDAT_0 (or the date dimension of your choice) >= 09.01.2023 without any null (empty) measurement since the data is calculated on the fly and not hardcoded as part as the OLAP loading.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage X3

Fiscal Year instead of Calendar Year in reports

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING