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
@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.
19 People are following this question.
