TIMESUM cant aggregate(e.g. MAX) time period on time period dimension?

CeciliaRen

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

Accepted answers

All comments

AlejandroGomez

Hi @CeciliaRen

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

CeciliaRen

@AlejandroGomez

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

AlejandroGomez

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

ChrisAHeathcote

@CeciliaRen

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

rob_marshall

@AlejandroGomez @ChrisAHeathcote @CeciliaRen

Good morning. First, @ChrisAHeathcote 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, @CeciliaRen, 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

AlejandroGomez

thanks for your input @ChrisAHeathcote,

Let me just point out that I tested this myself and I can ensure that the error message appears even though the Target and Source line items are correctly formatted. As per the Anapedia documentation, this calculation should work with the settings from @CeciliaRen ...

ChrisAHeathcote

If the MAX needs to reference the full year then you can not use zero as the end reference in the MOVINGSUM as this will move with the calculation. Using Zero will only bring the calculation upto the current period.
While the start is -Month, end would be 12-Month if you are to capture the full 12 months for each period.

rob_marshall

@ChrisAHeathcote

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

AlejandroGomez

we have learnt a lot about the planual, but we still do not know if that unexpected error message is a bug 🙂

Misbah

@AlejandroGomez

Well my hypothesis says that we can't use aggregation functions (SUM, MovingSUM, TIMESUM etc) on Time Period Formatted Line items if that particular line item has Time dimension in its module.

rob_marshall

@AlejandroGomez

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

Ingilavicus

Hi All,

I had a similar issue a while back. I contacted support thinking that this is a bug and we came to the conclusion, that it might be linked to the MAX function, where a number or a data format is required. At least that was consistent when I was trying to get a result out of TIMESUM using MAX.

But this of course doesn't match what @rob_marshall said that in his testing it indeed is a bug.

Would be quite interested what is the real reason.

Andris

Quick Links

Can you help?

Unanswered questions

Azure SQL Connection, database name with Hyphens in Data Orchestrator
I am attempting to connect to an azure SQL database vis at the MSSQL connector in data orchestrator but am having issues and I believe it is due to hyphens in database name (which is a fairly standard naming convention I will note!) Example below (fake credentials) - has anyone else encountered similar and is there a way…
12.4.1 Activity: Create Saved View for Non Employee Expenses
Hi, for some reason my shipping costs are the same over every month and I don't know what to do to fix it.
Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…