Current Date vs Week

[sanchoniathon]

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 !

[oscarca]

Hello,

You can extract week from Transaction date like extract(week,[Transaction date])

Best regards
Oscar

[bus_pass_man]

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.   

[dougp]

Define "week".  Do you mean week of the month?  ISO 8601 week of the year?  Or something else?

[sanchoniathon]

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!

[sanchoniathon]

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

[BigChris]

_week_of_year should sort that out for you

[dougp]

It looks like you are still confused.  Define "week".

Year --> Q1 --> Month --> Week# -->

That looks like week of the month.  But then you say you want week of the year...

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.

...but you are not certain how the business defines it.
Then you say...

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)

So the first day of every month is in week#2?