CEILING(CAST(DAYOFMONTH("Time"."T00 Calendar Date") as DOUBLE) / 7.0) (the cast is because of integer division ...)
This gives you the week number per month, concatenate it with a reference for the month and you have your week.
So something like 2017-03 week 1, 2017-03 week 2 etc.
(based on your definition of week you can't have week 1 to week 52/53 for the year as your weeks don't have 7 days each)
Thanks for the reply. I am working in the same way that you have suggested, it's working fine. I need the date on which max value of volume delivered in that particular week.
Scenario: For the year 2017 and January month i have taken.
columns are : Year, Month, Volume, date, week.
I need to get the max value (volume) dispatched (date) for week1, week2, week3, week 4, week5.
In the above screen shot Max values for week1 = 1,512 (1/2/2017), week2= 2,710 (1/10/2017) etc.
I need to get max value in one column and corresponding date in other column.
Week column has following formula:
CONCAT("- Shipment Period Dimensions"."Month ID", Concat(' Week ', cast(CEILING(CAST(DAYOFMONTH(cast("- Shipment Dimensions"."Attribute Date 7" as date)) as DOUBLE) / 7.0) as char)))
"Max volume" column has the following formula:
max("- Shipment Facts"."Gross Volume Base" by CONCAT("- Shipment Period Dimensions"."Month ID", Concat(' Week ', cast(CEILING(CAST(DAYOFMONTH(cast("- Shipment Dimensions"."Attribute Date 7" as date)) as DOUBLE) / 7.0) as char))))
Here i am unable to get the date on which this max volume dispatched. How to get the date?
First thing you are hijacking your own thread, making it a bit useless for others with similar questions as you change (add more) question in the same thread.
To keep things useful for everybody (it's a community forum) if you got an answer working for your original question close the thread marking as required. If you then have a different question open a new one (in this case your question isn't related to the week calculation at all as having a column for weeks from your DB wouldn't change the issue).
For you need I wouldn't use MAX but more do a rank on the values and filter to keep only rank = 1, the ranking is of course by week again. In this case you also have the date. If using MAX you can have the max value but as you saw no way to get the corresponding date.
My apologies for adding one more question to this thread.
Initially these two are requirements, but initially, i was unable to achieve week definition so i asked that question. Both are resolved. Thank you so much.