Hi,
We need to create a report that displays information by week.
For example:
Week# BusinessPlace# ItemsOrderedDuringWeek#
Week#1 253 502
Week#2 115 777
Week#3 333 885
The thing is that the we do not already have a time dimension to have the different levels like this for example
Year --> Q1 --> Month --> Week# -->
We only have DATES, meaning Transaction Date, meaning the DAY is our lowest level of granularity if i can say that.
Can we even display the data in a WEEKly manner as indicated above ?
Thanks !
Hello,
You can extract week from Transaction date like extract(week,[Transaction date])
Best regards
Oscar
Week isn't a valid token for extract.
You would need to use _week_of_year. This gives you the ISO 8601 standard week from the date value.
This may or may not be what is wanted. As always it is a good idea to establish what exactly is meant by week. For example, 2021-01-03 is part of week 53 of 2020. That may or may not be what is wanted. You might be wanting something like the standard retail calendar, with their weeks.
Define "week". Do you mean week of the month? ISO 8601 week of the year? Or something else?
Hi to all in this GREAT forum and thank you for the reply! I meant WEEK OF THE YEAR but then again i will make sure their not talking about week of the year based on the Financial Year. Thanks once again!
Just to make sure, This is what we have as for now :
Day BusinessPlace# ItemsOrderedDuringDay
...
05-01-2020 115 277
06-01-2020 115 100
07-01-2020 115 050
08-01-2020 115 050
09-01-2020 115 150
10-01-2020 115 125
11-01-2020 115 025
...
And this is the end result : For example for Dates 05-01-2020 to 11-01-2020 (assuming it is our week#2 for 2020),
then we should flatten the results for all the days on that week#2 to only have 1 rows for the whole week#2 with the sum being 777
Week# BusinessPlace# ItemsOrderedDuringWeek#
Week#1 253 502
Week#2 115 777
Week#3 333 885
_week_of_year should sort that out for you
It looks like you are still confused.
Define "week".QuoteYear --> Q1 --> Month --> Week# -->
That looks like week of the month. But then you say you want week of the year...
QuoteI meant WEEK OF THE YEAR but then again i will make sure their not talking about week of the year based on the Financial Year.
...but you are not certain how the business defines it.
Then you say...
QuoteDay BusinessPlace# ItemsOrderedDuringDay
...
05-01-2020 115 277
06-01-2020 115 100
07-01-2020 115 050
08-01-2020 115 050
09-01-2020 115 150
10-01-2020 115 125
11-01-2020 115 025
...
And this is the end result : For example for Dates 05-01-2020 to 11-01-2020 (assuming it is our week#2 for 2020)
So the first day of every month is in week#2?