We have a dashboard showing monthly and weekly trends with sheet swapping.
The monthly sheet has a parameter for 1,3,6,12 months rolling average(which calculates on monthly basis)
-Now my requirements as follows ,when i select Week sheet from the parameter ,
I want to have a rolling week trends as per the Rolling average for months parameter.
-i.e if i select 3 months from Parameter it would display 12 weeks rolling average, same on 6 months and 12 months.
I created 3 calculated fields for 12, 24 ,48 (based on month*4 week) weeks rolling average ,actually this is not correct.
Because if i select 12 months from rolling average parameter- the actual rolling weeks would be 52 or 53 weeks in this case my calculation for 48 weeks rolling average is wrong.
- So please guide me to achieve the result based on the calendar date if i select 3 months as per the calendar it would display rolling weeks for 3 months,same for 6 months and 12 months
An alternative approach will be to use number of days instead. You can calculate number of days for each selection and use the formula INT(no. of days/7). This will get you closer to the correct week calculation. For example; INT(365/7) will give you 52 weeks.