Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Determining Week of Year using Data Management Console
Go
New
Search
Notify
Tools
Reply
  
Determining Week of Year using Data Management Console
 Login/Join
 
Member
posted
Good morning all, I'm having some issues determining the week of year using IBI...

I'm trying to build out our date dimension. My source is a MySQL table that I created using using an Excel spreadsheet and building out attributes (such as Month Name, Day of Week, etc.) for dates from 1994-2030. One of the attributes that I have is Week of Year. This is calculated using the functionality in excel, and is based on a Sunday - Saturday week. Its my understand, based on what I've seen on the forum, and in my results, that IBI uses a different definition of week than that of Excel.

Ultimately, I'm trying to populate some Y/N flags, such as Week To Date, Previous Week, Prior Year WTD, and Prior Year Previous Week...the problem is that I can't compare the current date's week (that I'm generating in my SQL select) against each row's week number, because they are calc'd differently.

Has anyone done something like this before, and if so how? I read on the forum that people have made some hacks to "WEEKFIRST", but when I look at the text view of my code, I can't find where its setting Weekfirst. I'm new to DMC, so maybe I'm missing something here...

Thanks,
Kent


Developer Studio 7.6.11
Windows & Linux
all output formats
 
Posts: 8 | Location: Boston | Registered: April 17, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
Hi kent,

You can set the WEEKFIRST in a stored procedure in DMC.

It's not really IBI's interpretation of when a week starts, but more the region where you live in or whatever company standard you have.

Here, in the Netherlands, I always use SET WEEKFIRST = ISO2.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 399 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Kent,

Could you explain what Y/N means for Week To Date, Previous Week, Prior Year WTD, and Prior Year Previous Week?
thnx
Endre


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Its a flag that the end user will be able to set and use as a limit. For example, they'll be joining this date dimension to a file that contains sales by date for example. If they want to see WTD sales, they can just set a limit on the WTD column of 'Y'. If they want to see sales for the previous week, they would limit on that column as 'Y'. Does that make sense?

Thanks for the suggestion regarding the stored procedure Frans, but unfortunately I wasn't able to get that to return a week that matched the MS excel weeks.

I'm able to get pretty close to what I'm looking to due using Edward Wolfgram's coding suggestion from this topic, but I'm still not sure its going to work 100% of the time.


Developer Studio 7.6.11
Windows & Linux
all output formats
 
Posts: 8 | Location: Boston | Registered: April 17, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Determining Week of Year using Data Management Console

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.