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

CommunityMember123764
Occasional Contributor

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

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!

 

 

 

14 REPLIES 14
andre.lie
Certified Master Anaplanner

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

Hi @CommunityMember123764 ,

 

I think you need to assign the % Profile in the historical module to four different line items, one for each generic quarter, by checking whether the Quarters line item is equal to Q1, Q2, Q3, or Q4. You also need to create four separate count line items with value of 1 if the Quarters matches with the line item.

 

In the Weekly Profiles module, you can divide the sum of % profile with the count by either selecting Time.All Periods if Total of All Periods is enabled in the Time Settings or using TIMESUM if it is not.

 

Regards,

Andre

 

 

rob_marshall
Moderator

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

@andre.lie @CommunityMember123764 

 

Please do not use TimeSum when the target (the line item containing timesum) is dimensionalized by Time (Time in the Applies To) as this is a huge performance hit.  Instead, consider using MovingSum().

 

Link on MovingSum

 

Rob

andre.lie
Certified Master Anaplanner

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

Thanks for the hint @rob_marshall !

CommunityMember115881
Certified Master Anaplanner

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

Hi,

 

I can offer another solution, not the cleanest design but it seems to get the job done.

What I did is to first move away from real time with a Fake Time list (using LOOKUP and a simple mapping table which can be automated via formula). 

Then I aggregated the % based on their respective quarter (this is using a genereic Quarter list not time dimension).

 

INPUT:

CommunityMember115881_1-1611654289211.png

SOLUTION:

CommunityMember115881_5-1611654377520.png

SPEC:

CommunityMember115881_0-1611654153632.png

CommunityMember115881_2-1611654310370.png

CommunityMember115881_3-1611654338714.png

CommunityMember115881_4-1611654357989.png

 

@rob_marshall  is there a reason why we can't move away from the time dimension in one simple aggregation? (e.g. with SUM on a list formated line item)

 

rob_marshall
Moderator

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

@CommunityMember115881 

 

Absolutely, very happy to and this is a question we get a lot.  TimeSum is not meant to be used when the target (where you are doing the timesum) has Time in the Applies To

 

2021-01-26_07-22-58.png

 

Now, that doesn't mean it doesn't work because it does, but it adds additional overhead that is not needed.  Check out this video @MarkWarren and I put together last November.

 

https://www.youtube.com/watch?v=PvimWuX5jVw

 

 

Hope the helps

Function vs. Function:MOVINGSUM vs. TIMESUMWe're back with round 2 of Function vs. Function! This time, join Anaplan Live! host Chris Weiss as he moderates a...
CommunityMember115881
Certified Master Anaplanner

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

Hi Rob,

Yes I had seen the video, very interesting.
However that wasn't my question. In this particular case the target is not dimensioned by time but Fake Quarters (Q1, Q2, Q3, Q4 so not linked to any specific year). The problem would be the same with any other list really.
You can't seem to be able to remove the time dimension and aggregate onto another list (not part of the source 'Applies to' section) in one go...either that has changed recently or I had never noticed it before.

Cheers,

Douglas
rob_marshall
Moderator

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

@CommunityMember115881 

 

Sorry, I had misunderstood your question, I thought it was specifically why you shouldn't use TimeSum() when the target used Time in the Applies To.  If your target does not use Time, then sure.  But, from the looks of your pictures, you might be coming from an Essbase background, if my memory serves, where you split the weeks from the years into different dimensions.  Can you do that with Anaplan, yes, but the only use case I can think of doing that is for reporting purposes.  As for the moving from the Time dimension to a fake time dimension, you can do that, use your mapping and then do a lookup instead of a sum (if I am understanding your question).

 

Rob

ChrisHeathcote
Community Boss

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

@CommunityMember123764 

I do not believe you need to use TIMESUM or MOVINGSUM.

My suggestion is that you use a combination of AVERAGE summary option, ALL TIME PERIODS and SELECT to pull through the correct data into your target module.

 

  • Firstly, enable 'Total of All Time Periods,' in your model time settings
  • The update the summary settings for the % profile line item to, 'Average.'
  • In your target module reference the '% Profile,' line item in the source and use the following select function =Historical Weekly Profiles.% Profile[Select:Time.All Periods]

This will pull through the 'All Periods,' summary in the source and as the summary settings for this line item are set to, 'Average,' the average will be shown against the relevant week and quarter. Both quarter and week in this final module are lists NOT time settings so when you use this data in downstream modelling you will need to utilise the mapping present in the the other line item in the source shown in your query. 

 

ChrisHeathcote_0-1611745486519.pngChrisHeathcote_1-1611745528110.png

Good luck,

 

Chris 

 

CommunityMember115881
Certified Master Anaplanner

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

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.