You might be thinking what’s the mystery. I pray continue reading.
We all use formulas in Smart View, and I came across a recent issue when helping a user. He was using a formula to derive months and pull data from Essbase.
I’m trying to replicate the “issue” in good old Sample Basic.
That is how his sheet looks!!!
Aug, Jul, and Jun are there. However, they are not getting recognized by Essbase. I was thinking about
- Did someone change the member names?
- Is he using different alias tables?
- What is that date doing there?
After having a conversation with the user, I found that the months that show up on the sheets are not real months!!!!
What he did was to apply a custom format and made it look like MMM
Essbase was still getting the full date field and since there was nothing there in Essbase as a member name to return it replaced that with the default “Period.”
The correct way to get month name from date is using an EXCEL formula. TEXT(date_text,”mmm”)
There you go, and it now starts showing the exact numbers.
It was a fun exercise, I should say.
