October 6, 2003 at 2:15 pm
First, I'm fairly new to building calculated members in a cube.
Secondly, here's my problem(quest). I'm trying to create an expression that can sum data for services rendered on a given date, and paid for within three months of the service. In sql, this condition is a piece of cake, but in MDX I'm stumped. I'm looking for something like the following "concept":
Sum(amount_paid) where PaidDate<=(IncurredDate.currentmember beyond 3months).
The above example is reflective of my cube possessing two time dimensions: IncurredDate and PaidDate.
This whole equation reflects an aspect of our business where a customer receives a billable service from us, and then we usually receive payment within 3 months of the billed service. A 3-month runout allows us to take snapshots of our business that can be used to easily make YTD comparisons. Without the ability to limit aggregations to a 3-month runout, we lose the ability to "freeze" time.
Again, this 3-month runout is easy to do in sql, but I just don't know where to begin with an MDX expression.
All help comes with much appreciation in advance! --Pete
October 6, 2003 at 5:41 pm
Hi Pete,
I have a suggestion, that is not actually a direct answer to your question.
A quick solution to this issue would be to add a flag field to your source table/view that indicates whether the payment was paid within the appropriate time (ie use a datediff to give you a yes/no 1/0 result). You could then use this flag as a 2 member dimension, that would allow you to filter (say) Invoiced Revenue where it has/has not been paid withina time limit. This of course relies on the 3month window for payment being the only window you want.
An alternative to this could be to use a date diff again to create dimensional members that group payments into sets, say payments made in <=30 days, 31-60 days, 61-90 days and 90+ days. If you have a customer dimension in your cube, this could then be used to determine which customers are paying regularly within each receivables period.
HTH,
Steve.
Steve.
October 7, 2003 at 6:05 am
Steve:
Interesting ideas as alternative solutions. They may prove better than mdx! Even so, I'd still be thrilled to know what the mdx solution is to my problem. I find mdx to be a cool, but weird language to code. So, if you should derive a solution in mdx, feel free to post that too.
Thanks again Steve. I'll test out your suggestions.
--Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply