Taking an average of specific Quarters, like all Q1 or all Q2 over many years

CommunityMember123764

Hi, I'm attempting to generate some historical trends to drive future projections. In doing so, I have % Weekly over a 13-week period (one quarter), and I have this over many quarters. I'm trying to capture an average of all Q1 % weekly values (such as FY18 Q1, FY19 Q1, FY20 Q1, etc.), to predict a likely quarter projection by week for Q1 weeks.

This Historical module has the raw data (first snapshot), and each quarter has it's designated quarter value (second snapshot):

 

On another module, I'm attempting to capture all Q1 values using TIMESUM, but I don't know how to specify "Q1" explicitly:

Both tables have "Quarter" as a list and a dimension, but because the Historical module has Time, and Weekly Profiles module does not, I'm unable to do a simple SUM/LOOKUP to get my data. I'm having to try the TIMESUM to overcome this, but now I'm unclear as to how to only pull Q1 values. (I'm only reflecting FY20, but I have FY19 and FY18 I would be adding, and using those 3 years to average out Q1).

In both modules, the Week columns are a List and a dimension, not a Time Range.

Any direction would be much appreciated.

Thank you!

 

 

 

CommunityMember115881

Hi Chris,
This was a good idea however I believe what the user is after here are averages by quarter (e.g. Q1 2020 and Q1 2021 for week 1) and not for all quarters at once like you are suggesting.
The summary method set to AVERAGE gets you the average for the year, or all periods, for every quarters combined.
In your example, the Q1 average should be 2% for week 1 (average of 1% and 3%) and for Q2 week 1 should be 3.5% ( average of 3% and 4%). It shouldn't be the same value.

James Dougan

Finditem(Time,Name(item(Fake Q List))&" "&name(item(time)))

 

 

CommunityMember123764

Thanks for this James, the formula actually helped me really understand the usage of the basics of finditem, name, and item. I wasn't able to make it work the way this is now, since I think the formula assumed I needed time in both modules, but nonetheless the direction helped me find a solution, which I will post soon. Thank you!

CommunityMember123764

Hi Chris, thank you - I removed time from the Historical module and setup a Quarter FY list of just the time periods I needed. This allowed me to put everything together, similar to what you had!

Here's my final setup, where the time values on the rows are a list, not time:

So after removing the time dimension, I created a list "Time List: Quarter FY", and broke it into FY and Quarter.

In the summary module, I simply do an average based on the Quarter:

Right now I only have 1 year of data, so the "average" function isn't doing much, but hopefully once I get more data this will still hold true.

Thanks again!