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!

[andre.lie]

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]

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

Thanks for the hint @rob_marshall !

[CommunityMember115881]

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:

SOLUTION:

SPEC:

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

@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

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

[CommunityMember115881]

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]

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

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

Good luck,

Chris 

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