YTD Workaround?

I have an issue with the way Anaplan calculates YTD and need help figuring out a workaround. The YTD value in Anaplan includes the current week, which is NOT how I would like it or need it. i am in a weekly system and do not have actuals for the week I am in. So when comparing YTD Actuals against YTD Forecast it is always wrong and always undercounting the YTD Actual by the current week. I first submitted this as a bug only to be told this was the way it was designed. So I need a way to compare YTD Actuals to YTD - Current Week Forecast. They would both be YTD - Current Week.

Accepted answers

PaulRitner

Hi,

I'm flying blind here, but there are other ways to aggregate across time without using the YTD function.

One way is to have a line item that aggregates another value like this:

Line item 1 is the weekly source values

Line Item 2 is the YTD value, containing a formula like: Previous(Line Item 2)+Line Item 1 (if you have more than 1 year defined, you'll have to reset the clock in week 1)

If the Line Item 2 value is used in a report that doesn't contain time, you might have a selector that chooses which week to present, or the report can contain YTD values for all weeks, and have time as part of the report module... I prefer the latter, it allows users to look back in time.

If this doesn't help, please share some screen shots and we can take a deeper dive.

Paul

All comments

PaulRitner

Hi,

I'm flying blind here, but there are other ways to aggregate across time without using the YTD function.

One way is to have a line item that aggregates another value like this:

Line item 1 is the weekly source values

Line Item 2 is the YTD value, containing a formula like: Previous(Line Item 2)+Line Item 1 (if you have more than 1 year defined, you'll have to reset the clock in week 1)

If this doesn't help, please share some screen shots and we can take a deeper dive.

Paul

Jaakko

Hi,

I thought about this for a while and with week time scale Paul's solution with previous function is probably the simplest . With month time scale I have used the following solution.

Formula in separate YTD Line item:

YTD = MOVINGSUM(Source Line Item, -1 * MONTH(ITEM(Time)), 0).

If you don't want to include the last time member, last parameter would be -1 instead of 0.

I guess there is no function for week numbers in the same way than for months (MONTH-function above)? In that case for this solution you'd need a separate line item (for example in time assumptions module) where you'd calculate the week number for each week. This could be done, for example, with cumulate(1) function and resetting that in the first week of year. Or you could try to parse it from the time item name, but I am not sure if that is wise. 🙂 Then it would look this confusing:

Week Number =

VALUE(RIGHT(LEFT(NAME(ITEM(Time)), FIND(" ", NAME(ITEM(Time)), FIND(" ", NAME(ITEM(Time))) + 1)), 3))

And weekly YTD without the last member would look like this:

YTD = MOVINGSUM(Source Line Item, -1 * Time Assumptions.Week Number, -1).

Br,

Jaakko

andrewtye

How about a separate module that does the summarising up and using the TIMESUM formula... I have the same issue and generally don't use the pre-calculating such as YTD / YTG.

So the formula would be TIMESUM([Original Line Item], [Start Week], [End Week]) - with the Start / End week being in a separate module.

Particularly useful if you've got some non-standard roll-ups of time or want say last n weeks.

unknown

This content has been removed.

Quick Links

Can you help?

Unanswered questions

Prevent end users deleting a list item when using Worksheet in UX.
Hi, I have a worksheet for end users to enter new hires into, for which the list item which persist across versions. I am having an issue where the end user can delete a list item using the grid functionality because the Current Version value is blank, but I want to prevent them deleting them (as data will be lost from…
Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…