How to count weekly report

[Nice99]

Hi,
I am a new to design WEBi report, is there anyone have experience to create the report Count by week based on order date for months?

example:

Group name product order date

..... ...... 03/22/2011
..... ...... 03/12/2011
..... ...... 03/08/2011
..... ...... 05/08/2011
..... ...... 05/12/2011
..... ...... ..........


Thanks,

Nice99

 

[hilfy]

How are you defining week? Does a week start on Monday? Sunday?

If you look at a week as starting on Monday, something like this will convert any day to the Monday of the week it's in:

=RelativeDate([Order Date]; -(7-DayNumberOfWeek([Order Date])-1))

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Nice99]

Hi Hilfy,

Thank you so much for your help, yes, the report should start on Monday to Sunday, but the formula did not convert any day to the Monday of the week,

example:
I using Qualification: Dimension
Type: Date
Formula: =RelativeDate([Order Date]; -(7-DayNumberOfWeek([Order Date])-1))

It converted 11/30/11 to 11/27/11; 11/29/11 to 11/25/11….
I am wondering if the date format affects the result. Because in the date format from Universe is 8/22/11 not 8/22/2011, please advise! Thanks for your help!

Nice99

 

[Nice99]

Hi hilfy,

I think I can fix the Monday, Sunday, but the number of weeks of the Year, such as Week 1...Week 52, please let me know if you have experience. Thanks for your help!

Nice99

 

[hilfy]

Do you have access to update the universe? Getting the week number of the year is something that can usually be done fairly easily in SQL in the universe (depending on what type of database you're connecting to). I'm not sure how I would do that just through variables in a report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Nice99]

Hi hilfy,

I do not have access to update the universe. But I already fixed the issue. I found there are build in Functions such as Week, LastDayOfWeek, etc. from Variable Editor. Thank you so much for your help!


Nice99