This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
 


  << Previous Topic | Next Topic >>Return to Index  

Y-T-D with fixed values

February 19 2013 at 5:48 PM
No score for this post
Jeff 
from IP address 12.70.211.195

 
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.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply



204.101.190.178

Re: Y-T-D with fixed values

No score for this post
February 19 2013, 10:11 PM 

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?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Jeff

12.70.211.195

Y-T-D with fixed values

No score for this post
February 20 2013, 12:52 PM 

Javier,

Yes my billing days member is dynamic calc.

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   



199.198.223.108

Re: Y-T-D with fixed values

No score for this post
February 20 2013, 1:46 PM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Jeff

12.70.211.195

Y-T-D with fixed values

No score for this post
February 20 2013, 2:22 PM 

Yes it is sparse.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   



199.198.223.108

Re: Y-T-D with fixed values

No score for this post
February 20 2013, 2:54 PM 

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.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   



199.198.223.108

Re: Y-T-D with fixed values

No score for this post
February 20 2013, 2:55 PM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Jake Turrell

205.144.50.1

Re: Y-T-D with fixed values

No score for this post
February 20 2013, 3:42 PM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Current Topic - Y-T-D with fixed values
  << Previous Topic | Next Topic >>Return to Index  
 Copyright © 1999-2014 Network54. All rights reserved.   Terms of Use   Privacy Statement  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.