BusinessObjects Board

Week 1 should start from October 1st week

building reports webintelligence
#1

Hi,

Can anyone provide me a Formula in Universe designer (we use hana sql) to make October 1st week as week_number=1, since my Fiscal year starts from October to September.

I have to show last 4 weeks data from Current date while week_number should start from october.

coll (BOB member since 2011-11-03)

#2

I would strongly recommend building a calendar table within HANA to support this - there are tutorials on the net that show you how to do this, via your favourite search engine. :slight_smile:

You can then integrate this calendar table into your universe and make life far easier for yourself.

#3

Hi Mark,

We are not allowed to do so.

Can we get it through any Formula in Universe or Webi, based on Current date functionality.

We have Calendar Date only that too like ex: 201808,201807,201806 etc.,

Thnaks in Advance…

coll (BOB member since 2011-11-03)

#4

Your calendar date looks like a calendar month or week.

You’ll be able to create some logic with an If statement but you need to be clear on your own firm’s rules on when week 1 begins each year.

#5

Hi Mark,

Thanks for Quick Reply.

We have 201801, 201802, 201803…201830,201831,201832… Calendar week.

Our Fiscal Year Starts from the October to September, and the 1st week should be October first week. And even the header name of that column should begin with October 1st week as WEEK 1.

Thanks in Advance.

coll (BOB member since 2011-11-03)

#6

It might be best to keep the years and months separate. Something like:

[FiscalMonth] = if [CalendarMonth]>39 then [CalendarYear] else [CalendarYear]-1
[FiscalYear]  = if [CalendarMonth]>39 then [CalendarMonth]-39 else [CalendarMonth]

It depends when precisely the weeks start, so might not apply to future years.

mikeil (BOB member since 2015-02-18)

#7

Hi Mikeil,

Thanks for the Quick reply.

The Formula Provided by you, is not working as the present month is a 36 week number of the year.

And moreover, the formula should by default take up the 2019-October as week 1 and September of earlier year as week -1, week-2,week-3 data, when October 2019 FY comes into picture. Since i need to show last 4 months data.

coll (BOB member since 2011-11-03)

#8

The first full week of October or the week of October that contains October 1st?

#9

Hi Mark,

Thanks for Reply.

It’s the week of October that contains October 1st.

Thanks
Coll

coll (BOB member since 2011-11-03)

#10

Final question (I think):
Does the financial year 2018 start in October 2018 or October 2017?

#11

Hi Mark,

The Financial year 2018 is: Oct 2017 - Sep 2018

Thanks
Coll

coll (BOB member since 2011-11-03)

#12

Do you need to report by week? Week is more variable than month number

Once you have month number, you could build a simple case statement for both month number and year number

Pseudo code

Financial Period

case
when month number = 10 then 1
when month number = 11 then 2
when month number = 12 then 3
when month number = 1 then 1

etc etc


Financial Year

case
when month number in (10,11,12) then calendar year
else calendar year -1
end

jemstar :ireland: (BOB member since 2006-03-30)

#13

Hi Jemstar,

Thanks for Response.

But we are not allowed to write Psuedo Code. Could you let me know the formula in Webi or Universe level.

Thanks

coll (BOB member since 2011-11-03)

#14

Here is it is in Webi Formulas:

=If MonthNumberOfYear(Your Date Field) = 10 Then 1
ElseIf MonthNumberOfYear(Your Date Field) = 11 Then 2
ElseIf MonthNumberOfYear(Your Date Field) = 12 Then 2
ElseIf MonthNumberOfYear(Your Date Field) = 13 Then 3
ElseIf MonthNumberOfYear(Your Date Field) = 1 Then 1

Financial Year:
(Keep the CurrentDate() funtion within the Year() replace your date field in the MonthNumberofYear )
=If MonthNumberOfYear(Your Date Field) Inlist (10;11;12) Then Year(CurrentDate())
Else Year(CurrentDate())-1