Y-T-D with fixed values

Y-T-D with fixed values

Jeff
Jeff

February 19th, 2013, 10:48 pm #1

In my db, I track calendar billing days, and those are hard-coded using the @ISMBR function. Jan-13 then = 22.5, Feb-13 = 20, etc.

I tried updating the formula to include YTD days so that, for example, Y-T-D(Feb-13) = 42.5 but Essbase does not like that.

Is there a way to assign a valid to a Y-T-D Billing Days, where Billing Days is a member name in the Account dimension.

I do have Jan-13 as a member in my Time dimesion, while Y-T-D is an active Dynamic Series Member. Perhaps that is why the ISMBR formula works with Feb-13 but not with Y-T-D(Feb-13).

I have not been to any Essbase training classes, just picked up what I know from the person who did it before me, so hopefully I have listed enough info for a reply.

Thank you for your time.
Quote
Share

Joined: April 13th, 2011, 5:00 pm

February 20th, 2013, 3:11 am #2

Is "Billing Days" a dynamic-calc member?
Without making changes to your formula, what does "Y-T-D(Feb-13)" return when you retrieve it?
Quote
Like
Share

Jeff
Jeff

February 20th, 2013, 5:52 pm #3

Javier,

Yes my billing days member is dynamic calc.

I receive #missing when populating billing days using Y-T-D.
Quote
Share

Joined: April 13th, 2011, 5:00 pm

February 20th, 2013, 6:46 pm #4

Is your Time dimension Dense? It seems like it is Sparse.
Quote
Like
Share

Jeff
Jeff

February 20th, 2013, 7:22 pm #5

Yes it is sparse.
Quote
Share

Joined: April 13th, 2011, 5:00 pm

February 20th, 2013, 7:54 pm #6

When handling dynamic calculations, Essbase runs the Sparse dynamic calcs BEFORE the Dense dynamic calcs.

The YTD values are being calculated before it executes your "Billing Days" formula. As a result, it's adding #MI Billing Days to get the YTD values. #MI + #MI = #MI.

My suggestion would be to change "Billing Days" to a stored member and have a new Dynamic Calc member (eg: "Billing Days 2") point to it. (Or rename the members so that "Billing Days" remains Dynamic Calc but it points to a stored member.)

You can change your Time dimension to Dense. But I wouldn't recommend it as there's usually a reason for someone to set Time to Sparse. Check with that person. Maybe it was set on a whim.
Quote
Like
Share

Joined: April 13th, 2011, 5:00 pm

February 20th, 2013, 7:55 pm #7

Sorry, I should have clarified that the second suggestion (changing Time to Dense) is a NOT recommended alternative to the first option.
Quote
Like
Share

Jake Turrell
Jake Turrell

February 20th, 2013, 8:42 pm #8

In my db, I track calendar billing days, and those are hard-coded using the @ISMBR function. Jan-13 then = 22.5, Feb-13 = 20, etc.

I tried updating the formula to include YTD days so that, for example, Y-T-D(Feb-13) = 42.5 but Essbase does not like that.

Is there a way to assign a valid to a Y-T-D Billing Days, where Billing Days is a member name in the Account dimension.

I do have Jan-13 as a member in my Time dimesion, while Y-T-D is an active Dynamic Series Member. Perhaps that is why the ISMBR formula works with Feb-13 but not with Y-T-D(Feb-13).

I have not been to any Essbase training classes, just picked up what I know from the person who did it before me, so hopefully I have listed enough info for a reply.

Thank you for your time.
While order of calculation may be part of the issue here, I believe the root cause is that you can't refer to DTS members in a member formula. They're "virtual" members. The only way to get a formula to apply specific logic to a DTS member is via "exclusion" in an IF statement.

Here's how it works - in your formula you have conditional logic that says if you're one of these time period members (and this logic must include ALL time period members) . . then execute some logic. Then at the end you have an ELSE statement. It is in this ELSE section (because you have already excluded all "real" time periods) that you can include logic to be applied to DTS members. But here's the catch . . . you can't differentiate between different types of DTS members. So a YTD member would get the same treatment as a QTD member, and so on.

Because you cannot differentiate between different flavors of DTS members in this logic, this solution typically doesn't cut it. But beyond that, you're out of luck. You can't reference a DTS member in a formula.

Hopefully my rambling post makes sense and I understood the question correctly.

- Jake
Quote
Share


Confirmation of reply: