Dear All,
According to Anapedia,
"Line item to aggregate
The value to aggregate over time. The Line item to aggregate argument can be a line item of any format."
But, when I try to aggregate time period line item as follows, it returns an error.
Example
Timesum Date = TIMESUM(Date, TIME.'Mar 18', TIME.'Jun 18', MAX) -> successful
Timesum Period = TIMESUM(Period, TIME.'Mar 18', TIME.'Jun 18', MAX) -> error
error: Timesum Period has TIME PERIOD format with a Half-Year period type but the formula resolves to TIME PERIOD format with a Half-Year period type
Period | Date | Timesum Date | Timesum Period | |
Mar 18 | May 00 | 2000-05-01 | 2001-07-01 | |
Apr 18 | May 00 | 2000-05-01 | 2001-07-01 | |
May 18 | Jul 01 | 2001-07-01 | 2001-07-01 | |
Jun 18 | May 00 | 2000-05-01 | 2001-07-01 | |
Jul 18 | 2001-07-01 | |||
Aug 18 | 2001-07-01 | |||
H1 FY18 | 2001-07-01 | |||
Sep 18 | 2001-07-01 | |||
Oct 18 | 2001-07-01 | |||
Nov 18 | 2001-07-01 | |||
Dec 18 | 2001-07-01 | |||
Jan 19 | 2001-07-01 | |||
Feb 19 | 2001-07-01 | |||
H2 FY18 | 2001-07-01 |
Best wishes,
Cecilia
This is an interesting one. While I cannot quite understand the error message here, at least I have found the way of avoiding the error:
Use the PERIOD and START functions for your Timesum Period formula, like this:
Period(start(TIMESUM(Period, TIME.'Mar 18', TIME.'Jun 18', MAX) ))
This should give you the result you are looking for.
Cheers,
Alex
Hi,
Thank you for your idea!
Or firstly timesum date line item and then convert it to period.
But the error message really does not make sense.
Is it a bug?
best,
Cecilia
I cannot tell whether is a bug or not. But the error message does not make sense to me and I cannot find anything about it on neither Anapedia or Known Issues
Lets call the big guns, @rob_marshall can you tell whether this is a known issue or some sort of bug?
Thanks
Alex
we have learnt a lot about the planual, but we still do not know if that unexpected error message is a bug 🙂
Alight, alright, alright. Yes, in my testing, it is a bug as it should work, but that doesn't mean you should do that way.
Rob
Firstly, avoid using TIMESUM when the target is dimensioned by time. This function should only be used when the target contains no time dimension.
In you your case we should use MOVINGSUM as the target contains a time dimension.
From your query I would suggest that the the source and target line items are not using the same format.
Note, that if you are to use MOVINGSUM the start and end period must be numbers which are relative to the time period.
Therefore, you will need to calculate these in a time systems model to ensure that you cover the full time period under review.
To achieve this create three line items in the month time systems module all formatted as number;
Month Count = IF 1 + PREVIOUS(Month Count) > 12 THEN 1 ELSE 1 + PREVIOUS(Month Count)
Start Month = 12 - ( 11 + Month Count )
End Month = Start Month + 12
Example - March, start month -2 = 12 - ( 11 + 3 ), end month 10 = -2 + 12
Use these line items to inform your MOVINGSUM function.
Good luck,
Chris
@AlejandroGomez @ChrisHeathcote @litterbarbie
Good morning. First, @ChrisHeathcote gets the gold star for the day in using MovingSum() instead of TimeSum() as TimeSum() should not be used when the target is dimensionalized by Time. Second, @litterbarbie, I am not exactly sure what you are trying to solve, can you describe what you are after? If you are trying to get the max date for the current year, you can use this: MOVINGSUM(start, -Month + 1, 0, MAX)
Hope this helps,
Rob
Yep, I was doing the calc from the beginning of the year to the current period of the time scale. And, if you wanted, you can change the summary to Max and then just reference the full year. Either way.
Rob