Looking for Best Practice in combining text parameters and monthly amounts
There's a situation where I've seen in many models and already there in the two companies I've worked with Anaplan.
I've also seen several solutions, none of them being great. I'm looking to the community to see if anyone has a better idea or if there's already a Best Practice available for this. I tried looking for it, but there are several ways to phrase it, so hard to pin point.
The situation is the following:
We often use number lists so that users can create things like, say, Business Events. In this example, they would create a new Business Event impacting their forecast, every time they have one. Generally we make one model that serves as a "form" where they give more information about it (such as a name, who created it, who approved it, which part of the org it affects, comments section, etc). So that would be a module with text or list selection line items for filling out the details. Then we publish a different module where they would fill in the monthly amounts of impact for that specific Business Event, and those are the numbers that would be going to the forecast at the end of the day.
Well, users usually also want to have all those infos together in an overview report. Meaning they would have the details, followed by the monthly amounts on the same table.
I'm looking for the best solution for this type of request.
So far I've seen / done the following two options:
1. Create a list with the parameters you want to show and the months in sequence (then have actions to update the months on that list); create a module with a number line item to collect the amounts to the month list items, and then another line item formatted as text that will collect the text from the parameters module for non-month list items, and looks up the values collected before for the month list items. Looks bad since the numbers are made into text, so no formatting. And also limited formatting options since it's all in one line item (no different formatting and no different column size settings possible). Not such a big deal to maintain after it's there since the actions take care of keeping the right months on the list.
2. Create a module with the parameters as line items, but also where the time dimension is applicable. Then an additional line item for the Amount. Have the parameters text info show in every month, and set up summary so that it shows the same text on "all periods" field. Pivot so that the columns show first lines items, and then time periods below. Create a filter that will show only "all periods" for the parameter-related line items, and show all months for the Amount line item. From my experience, this was always bugging when there was a change in the time scale, the filters would not work properly (maybe it was done wrong also, not sure). And takes up a lot of useless space since you need the text line items combined with time dimension.
Hope I made myself clear, but let me know if it's not and I can try to rephrase it. Looking forward to see if anyone can share any good ideas about this situation which comes around so often!
Yes, that's exactly the issue! What you proposed is similar to the solution 2 I added, but indeed I didn't think about using the DCA for making it look cleaner, nice one! Still takes up quite a lot of "unnecessary" space I guess though, right? My models today have 4-5 years of data, plus since these are text line items, they generally take up more space I think. Does DCA save space when data is hidden? The biggest issue is that end users would prefer to look at things side by side. So in your example, the line items would be pivoted to the columns together with the months. Then the trick is to have a filter that shows only month one for the text line items and all months for forecast amount line item. Generally I had some filter applied that did not work well, and we ended up having to hide/show some columns and it always got ruined when we switched years in the model. Do you have any suggestions on how to make a good filter for that?
Here's another idea. Still not the best but it gets everything on the same row.
As @nathan_rudman suggests you can remove the calendar and you can denormalize the sales forecasts and create a line item for each month. I set it up so there's no hardcoding - it's based on whatever is the first month of the calendar. here's the grid and blueprint. I did the first four months. The only other way I can honestly think to get this aligned is to export to Excel using the free Anaplan Add-In. Well, hope this is closer to a solution for you.
Re: Looking for Best Practice in combining text parameters and monthly amounts
Thanks Jared! Indeed this one is the better looking one, although it requires more maintenance if they need more than one year of data (although maybe we can do Current year then then CY+1, CY+2 etc and reference to the time settings so it's always updated). I'll definitely suggest this to my users and see what they will prefer. Really appreciate all your suggestions! Gave me lots of food for thought.