Determine Fiscal Week, Fiscal Period Week, Fiscal Quarter from M_FISCAL_CALENDAR in HANA View

[former_member387287]

Hello Guru's,

I have a requirement to provide the granular level data for the Finance reporting. The requirement is that user should be able to drill data to the level of Week of Fiscal Year/Fiscal Period. I have generated a Time dimension table with Fiscal calendar M_FISCAL_CALENDAR

We are into retail business and our Fiscal Year is from March - Feb. So I have created a Calculation view (Time view by Fiscal Date) on top of M_FISCAL_Calendar. But the table has Variant, Date, Fiscal Year and Fiscal Period and I would like to explode this view by adding new calc columns to determine Fiscal Week (W##-YYYY) and Period Week (W##-P##), Quarter. I tried to use functions

1) For Fiscal Week - WEEK(DATE) but the data is wrong as it is taking Week 1 from January 1st (Probably considering it as Calendar year), where as it should count/show the week 1 considering the date from Fiscal period 1 (Considering the Fiscal calendar). Below is the example of FY2018 period start.

Calendar Month Fiscal Period Period Start Date Period End Date # of Weeks March Period 1 27-Feb-17
02-Apr-17

2) For Quarter, I have used QUARTER(DATE,4) but the data being showed is considering the quarters for the Calendar year, where March is being showed as Q1 and April as Q2. where as March, April and May should be the Q1 based on our Fiscal year.

I am trying to achieve this on the HANA view, so that I can create the input parameters on top of these to run the reports for Current week/Priorweek, Current period week/prior period week, Current Quarter/Prior quarter/Prior Year quarter.

Columns needed:

WEEK : Week# in Fiscal Year (March - Feb)

QUARTER - Quarter based on Fiscal Year (3/4/5 - Q1, 6/7/8 - Q2, 9/10/11 - Q3 and 12/1/2 - Q4)

PERIOD WEEK - Week # in that period

Let me know the approach and steps to determine those fields.

HANA 1.0 - Version 2.3.35

BW on HANA

Calc View -

Thanks

[former_member387287 · Accepted solution]

Hey Anindya,

Thanks for the response. As I mentioned that I have generated M_FISCAL_CALENDAR and had challenge to achieve the requirement (example-Quarter/Week (fiscal) and / Week (Fiscal Period)). I was able to do a similar logic that you have showed for Quarter by doing it as below.

quarter-fiscal-period.jpg

And for weeks, for our calendar the start date is not constant and I had to determine the start date in the Fiscal year and Fiscal period and then use the daysbetween() to get the weeks and now I am getting the output as needed.

output-fiscal-dimensions.jpg

Thanks,

Shashi

[anindya_bose]

You can build a calculation view on top of M_TIME_DIMSNESION table to generate quarter with custom logic .

You can already see the fields I selected from the table in screenshot above, screenshot of the formula as below .

For week, you can use daysbetween() function to get the number of days between Mar-01-XXXX and any other given day and then divide the result with 7 and do some maths to find the week number . Once all things are done, you can use the calculation view to load the data to persist the result in a table. That way, you do not need to calculate these on the fly and can save some time and memory .

Regards

Anindya