- Home /

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

Comment

@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.

Database Creation 1 Answer

How do you create a non clustered index? 6 Answers

Data Normalisation Query 1 Answer

sql question about sales time 1 Answer

Copyright 2019 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges