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