question

PRATHISH avatar image
PRATHISH asked

Hi All, I am looking for a method to calculate min, max and avg of customer spend each week in the last 90days by grouping it to bi-weekly data.

Hi All, I am looking for a method to calculate min, max and avg of customer spend each week in the last 90days by grouping it to bi-weekly data. Wherein I should be able calculate the min/max/avg spend between any two consecutive weeks and also we need an additional field which shows the two weeks that are considered. :)

@jeffmoden

databaseoracle-sql-developerdata-warehousegrouping
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@PRATHISH This request does not appear to make much sense. There are 14 days in each 2-week period you would like to group by. 90 is not divisible by 14, so you will always have one period which does not include the whole 2 weeks period. Why do you need the data grouped so unevenly? Also, you mention the additional field to show the 2 weeks that are considered. What does it mean when it is a single field? Please clarify.

You are probably looking for a way to add the custom by-weekly period to the calendar table, but this only makes sense if you first implement a calendar with the whole number of weeks (manufacturing, financial, etc.). Those calendars have 364 days for most years with occasional 371 days, so for most years there are 26-week semesters (half years), which you can divide into 13 by-weekly periods. If it is a DW solution then the "grouping" will be taken care of automatically, and you can add your own MDX for calculations for usually required time related measures.

0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.