Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
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, 2009Report 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: 454 | Location: Europe | Registered: February 05, 2007Report 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, 2006Report 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, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders