I’m using the Carry Forward option in my forecast versions. But I can’t find a way to determine, inside the template, which month is set to be the carry forward month. I’d like to use this as a way to indicate which months are ACTUAL vs PLANNED. (I can use a separate named set, which I may just have to do, but it would be much more accurate (and error proof) if I could read the value of the Version’s “Start Date” attribute, like we can with other member properties.)
Can anyone point me in the direction of accomplishing this?
I unfortunately don’t think that there is a way to do this. What I like to do is the following:
Create a Named Set contained Actuals through “x” time period. Example, if you have actuals through May, then you would put the May time period in that Named Set.
Have a header in the report stating something like, “This report contains forecasted data made up of Actuals through (insert named set here) with forecasted/budget for the remaining months of the calendar year/fiscal year.”
I find that having this verbiage provided a nice and straight forward explanation for what the report is showing without causing confusion. All that it takes is putting in the verbiage and updating the named set each month.
Another option we’ve used in Implementations is to
a) have another data view posted (and these rows/columns are hidden) that points to a single named set, which points to the month when Budget/Forecast begins (i.e. version start date),
b) have a formula in the columns (assuming this is where you have months going across in the template) where you do a vlookup for the last 2 digits of the time name (say 2021M02) and check if this is less than or equal to the named set.
c) and within this formula, include a IF formula that assigns a “ACTUAL” or “FORECAST” value accordingly
the other option is just to have two named sets, one for actual and one for forecast months, and have them in the columns with an “actual” or “forecast” listed above the month named set.
@navin.sadarangani.1, this is basically the approach I had adopted, which is basically restating the “Carry Forward From” data in a different form. The two together get us the data we need to get the results we need. I was hoping there’d be a more directly correlated method than this, because this requires an error-prone human not to forget to update two different configurations in the data model (e.g. a time named set having to match the starting month of the version).
All of the workarounds mentioned here are functional, but none of them overcomes the basic problem of having to make sure multiple independent configurations are kept in sync.
I’m building out a company-wide planning model where we will have dozens of this type of thing due to the different types of planning, and making sure all of these different cubes’ configurations are kept in sync, and without the ability to access the starting month as a member property (or some similar method) effectively doubles the number of configuration options and makes every template much more complex than would otherwise be necessary.
It is what it is, unfortunately. I was hoping I was overlooking a way to do this in a more straightforward way, but based on this thread I can see that we’re already on the right track.
Once I get some more of my votes returned, I’ll add this to the UserVoice.
I’ve landed on another idea that I will be testing out soon to see if it hangs together. Sometimes my ideas work out better in my imagination than they do in reality.
I was thinking of introducing a flag account with a ‘1’ in the actual version and a ‘0’ in the plan version (or vice versa; not sure it matters), then set the ‘1’ for every time period in the cube. Then, i can include that account in my dataviews, and i can key off that value to indicate whether a time component is a plan or an actual using a template formula.
I like this approach because, if it works, it overcomes the potential discrepancy by having two different manually-configured values having to match; it also gives me a way to verify that the version has been updated properly using a report, rather than having to visually inspect the model manager attributes.
I’ll probably be able to try this out one day next week.
Turns out some other emergency requests came up so i will probably get to this next week.
Meanwhile, I did create a report of all my named set settings. Having a hard time making sure all my different named sets have the correct values. It’s not only disruptive to my users’ productivity when I get them wrong hut it’s personally embarrassing!
This month I specified June 2019 as “last month” because I didn’t catch the year.
So now I have a report that lists all my named sets and their values, plus a flag if it isn’t the typical value in relationships to other named sets or even the current date. (If “this month” isn’t equal to the current system month; it gets a flag. It may be correct, but not usually.)
I have this report sent to me as a binder at the tail end of my monthly rollover workflow. Took me a while to develop and I have to add each named set specifically, so I’ll have to maintain it over time as I add new named sets. But it’s already proven useful and it makes me feel a bit more confident that our system is properly configured.
I also note that using the “carry forward from” option in a version slows down the template when you open it. It slowed the function down so much that I prefer not to use it too much.
Using the named sets is a good idea, but if you would like to have a unique combination (so for one version only the total for the year) then you can not use the named sets
The idea of a report with all the named sets and their values is a great idea!
I hadn’t noticed a great slow-down in performance; I’ll keep a lookout for that because we ARE having many slow-downs related to other causes, and this may be compounding them. Thanks for the feedback.
Here’s the approach that I have finally adopted. It’s taken a while to decide this is the best approach, but now that I’ve got it in place, I’m using it voraciously.
For the working plan, I use the “carry forward from” option to show actuals for prior months and working plan for current and future months.
I have an account in my tree with the sole purpose of giving me a flag for each period, letting me know whether it’s an ACTUAL or PLAN month. This is similar to what I was thinking in an earlier post, but I found a way to “set it and forget it” without any maintenance, by making it a calculated account.
Time conversion = use last value, including empty
Calculation varies by Rule Set
For ACTUAL, formula is 1
For PLAN, formula is 0
In my templates with Time as Columns, I include this account as a specific member, usually hiding it, but using it as the basis of a formula function to put in a label of “Plan” or “Actual” in the header area.
This approach works well for those reforecast templates/reports that work best with inline representations of actuals replacing the plan. (For variance analysis reports, we display the plan and actuals side by side, so in this case I’m not using the WPLAN version but another PLAN version that has the historical plan values.)
