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

Options
CommunityMember123764
CommunityMember123764 Member, ALL USERS, GroupMember Posts: 5 Occasional Contributor
in Planning & Modeling

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):

CommunityMember123764_1-1611275511325.png

CommunityMember123764_2-1611275553323.png

 

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

CommunityMember123764_4-1611275755550.png

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!

 

 

 

Tagged:

Comments

  • CommunityMember115881
    CommunityMember115881 Member, ALL USERS, GroupMember, Partner, Certified Master Anaplanner, Certified Model Builder Posts: 5 Certified Master Anaplanner
    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
    James Dougan Member, ALL USERS, Partner, Certified Master Anaplanner, Certified Model Builder Posts: 2 Certified Master Anaplanner

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

     

     

  • CommunityMember123764
    CommunityMember123764 Member, ALL USERS, GroupMember Posts: 5 Occasional Contributor

    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
    CommunityMember123764 Member, ALL USERS, GroupMember Posts: 5 Occasional Contributor

    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:

    CommunityMember123764_1-1611887909839.png

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

    CommunityMember123764_2-1611887946468.png

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

    CommunityMember123764_0-1611887858704.png

    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!

Categories