Sometimes we don't realize how many assumptions and perceptions we have when looking at something.
In this entry, Bill addresses a deceptively simple request that is a lot more complex to implement than it looks at first sight.
The proposed solution is not yet final, as it entails some manual processing, but maybe the community finds some value in it.
 

"Hey, Bill. One of our users has requested that we create a report, and I'm having trouble with it."  Ellie Nakamura, Bill's counterpart in Finance, showed him her tablet. 

"What seems to be the problem?"

"They want a report on the rolling forecast and expect to see the cost value when available, and the forecasted value if the month is still open."

Bill looked at the screen. "What have you tried so far? Your numbers look reasonable to me..."

"I entered a simple formula in the report that will pick the forecast value if cost is zero, otherwise the value in the Cost metric:

Amount=If(Cost=0,Forecast,Cost)"
,
"And what's wrong with that? It makes sense."

"Well, they say that sometimes there are partial values in Cost, and that they cannot accept that logic, as that period should still be open. Thus, the report should display the Forecast metric instead. The user said that it should be easy to figure out that, if have selected February in the Date, the report should display Cost until February, and Forecast afterwards." 

"I see." Bill looked at the screen again. "Yes, visually it is very easy to see, but you're saying there are issues..."

Fig. 1 - Visual representation of a 12-month period centered on the current month

"Indeed! I can extract the current date programmatically and try to calculate an offset. I have done in the prototype, prior to adding the month columns. However, when I add them, it doesn't work anymore."

"Yes. I guess the calculation engine moves the time slider virtually, and sets it to every month in the range."

"Exactly, Bill. I have scanned the documentation and the library functions, but have found no formula that returns the value in the Date Range box."

"You're looking for something like an anchor that will keep you fixed in time, and lets you choose between Cost and Forecast depending on where that virtual slider is."

"I have tried everything I can think of, but I'm at a loss for a solution."

"Tricky indeed. Maybe we can try something different. What if we had a table that would tell us whether a period is closed or still open?  Would that work?"

"Let me think about this." Ellie typed on her tablet for a short while. "OK, I have created a spreadsheet with the current status of the months. Now what?"

Fig. 2 - Sample closure status table (Month Status)

"Maybe you can query that table from the report and figure out the closure status of every period. That should give you the logic to decide between Cost and Forecast."

"Let me give it a shot, Bill. I need to do this on my computer: the tablet is fine for presenting, but not for coding."

 

Ellie came back a while later. "Something is wrong with this lookup call. I cannot get it to work. Look, I first created a column called This Month, and then tried to look it up in the closure table, but I'm not getting any results:"

This Month = CurrentDate("MMM-yy")
Status = Lookup(This Month,Month Status,Current Month,Current Status)

"The Status variable remains empty! The documentation says that Lookup() is not recommended in report formulas and that we should use inference instead, but it doesn't say it won't work!"

"That's a bit strange, but maybe it's a feature they have dropped since they published that document."

"What do they mean by inference, Bill?"

"I guess it means leveraging the model itself, how the metrics flow from an object to the next. Have you tried adding the Current Status column to the report directly, without the Lookup call?"

"I'll try right away." Ellie worked for a short while, her hands moving around as she dragged and dropped stuff in the environment. "It doesn't work, Bill. It says: 'No relationship between Applications and Month Status'."

"I guess it was to be expected: there are no relationships between Applications, the object you're using for the report and the month status table."

"So, what can I do? How to overcome this issue?"

"I've been thinking about this inference business, and maybe we can add the column you need to Applications Master Data."

"You mean replicating the formulas in the report into the master dataset?"

"Exactly, Ellie. Not elegant, but it should work."

"Let me give it a shot and I'll come back with the results."

Ellie came back some time later. "It works, Bill This is great! Now I can deliver what the user requested."

"There's a drawback, and that's why I said it was not elegant. We need that ancillary table with the closure status of every month."

"Isn't there something we can do about that?"

Bill smiled. "I'll do some research. I am pretty sure there's a way to figure that one out. I'll let you know when I find it.

Copyright (c) Guillermo Cuadrado, 2022


​​​​​​
#BillTheTBMGuy
#ApptioforAll