Year and Week Date!

building reports webintelligence
1

Hi All,
I have a column called Week/year which displays data as week of the year. eg: “201323”
So 2013 is year and 23 is the week of the year. I need to show it as Date. My backend is a Bex Query and I am using BI Launcpad using BICS to connect with BW.

I will appreciate any input on this!!.. :roll_eyes:

americanmc :hong_kong: (BOB member since 2009-12-31)

2

Look at the Webi formula ToDate for starters.

Andreas :de: (BOB member since 2002-06-20)

3

Hi,

Which date? A week has 7 days.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

I tried to use ToDate function in webi, but it does not recognise “201323” as Date.
I want it to show as the weekend Date, that is Saturday in Webi.
Appreciate the input! :blue:

americanmc :hong_kong: (BOB member since 2009-12-31)

5

Hi,
I’ll assume that you can’t just pull in the date from your data source (not sure wih BEX limitations). If it’s a requirement, then it needs to be in your data source.

One option would be to add another data provider to your report that had your year and weeks tied to Saturday (or other) dates for use in the report to display.

Otherwise you may need to consider an extensive somewhat crazy variable something like this;
[list]=
If(Substr([Your Date Field];5;2)=“01”;“1/5/2013”;
If(Substr([Your Date Field];5;2)=“02”;“1/12/2013”;
If(Substr([Your Date Field];5;2)=“03”;“1/19/2013”;
If(Substr([Your Date Field];5;2)=“04”;“1/26/2013”))))[/list]
note this code only works for the first 4 weeks of 2013.

Joe Szabo :us: (BOB member since 2002-08-19)

6

Hi,

Will a formula like this work in this case?

=RelativeDate((LastDayOfWeek(RelativeDate(ToDate(Substr([your date;1;4);"yyyy");ToNumber(Substr([your date];5;2))*7)));-1)

Marek Chladny :slovakia: (BOB member since 2003-11-27)

7

[b] :+1:
Excellent!.. this formula worked PERFECT!!!
Thanks and regards

:wave: [/b]

americanmc :hong_kong: (BOB member since 2009-12-31)

8

A nice and elegant solution Marek!!!

Joe Szabo :us: (BOB member since 2002-08-19)

9

Thank you, Marek! :+1:

srodgers :us: (BOB member since 2013-06-04)

10

I am very new to Web Intelligence and still mastering the basics. I have no formal technical training and all of my experience with report writing has been in excel. I am still figuring out how to translate what I know about pivot tables, vlookups and excel’s syntax into WIXI. I have absolutely no database experience or with writing queries in SQL but I love having the opportunity to learn.

I needed to modify Marek’s solution in order to complete one of my first projects in WIXI. Perhaps someone can benefit from this (unlikely) or can provide a more elegant solution (more likely).
I have a report that runs from Friday to Thursday of the following week and is dated that Friday. I started by creating a new variable that uses the Day Name to move Friday, Saturday and Sunday to the next week:

=If([Open Time (Day Name)]="Friday" Or [Open Time (Day Name)]="Saturday" Or [Open Time (Day Name)]="Sunday";ToNumber([Open Time (Week Nr)])+1;ToNumber([Open Time (Week Nr)]))

Then I switched the second part of Marek’s formula to point to the new variable:

=RelativeDate((LastDayOfWeek(RelativeDate(ToDate(Substr([Your Date];1;4);"yyyy");([New Variable]*7))));-16)

Since 2013 began on a Tuesday, I adjusted the relative date to -16 because I needed 1/1 through 1/3 to count as the first week of the year but display as the week beginning 12/28/12.
I consider this a good-enough-for-now solution but I have already found a problem: the formula doesn’t work for multiple years.

[Moderator Edit: Added code formatting - Andreas]

srodgers :us: (BOB member since 2013-06-04)