Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

MDX to calculate Ongoing from ValidFrom and ValidTo

0 views
Skip to first unread message

Jerome

unread,
Dec 15, 2009, 5:14:01 AM12/15/09
to
Hi,

I have a cube with two fact tables with contracts och orders (one row per
order/contract) that both have ValidFrom and ValidTo dates (implemented as 4
separate dimensions).

Is it possible to have a separate time dimension (year, month), not
connected to the facts, and two calculated measures that counts the number of
ongoing contracts and orders for each period ? Contracts and orders are <
1000 so its possible to have them as dimensions if needed.

Month Ongoing contracts Ongoing Orders

Jan 12 23
Febr 14 26
March 9 19
...

/Jerome

brettmcgirr

unread,
Dec 15, 2009, 10:09:25 PM12/15/09
to
Have you had a look at this blog?

http://sqlblog.com/blogs/mosha/archive/2007/06/01/counting-in-flight-events-in-mdx.aspx

It sounds like something that could help.

Cheers.

Jerome wrote:

MDX to calculate Ongoing from ValidFrom and ValidTo
15-Dec-09

Hi,

....

/Jerome

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to Run ASPNET_REGSQL on your Shared Hosting Site
http://www.eggheadcafe.com/tutorials/aspnet/c71593ff-b7ba-41ba-a0f9-ee40da8ecd88/how-to-run-aspnetregsql.aspx

Peter Sidi

unread,
Dec 17, 2009, 11:11:42 AM12/17/09
to
On Dec 15, 10:09 pm, Brett McGirr wrote:
> Have you had a look at this blog?
>
> http://sqlblog.com/blogs/mosha/archive/2007/06/01/counting-in-flight-...

>
> It sounds like something that could help.
>
> Cheers.
>
> Jerome wrote:
>
> MDX to calculate Ongoing from ValidFrom and ValidTo
> 15-Dec-09
>
> Hi,
>
> I have a cube with two fact tables with contracts och orders (one row per
> order/contract) that both have ValidFrom and ValidTo dates (implemented as 4
> separate dimensions).
>
> Is it possible to have a separate time dimension (year, month), not
> connected to the facts, and two calculated measures that counts the number of
> ongoing contracts and orders for each period ? Contracts and orders are <
> 1000 so its possible to have them as dimensions if needed.
>
> Month           Ongoing contracts       Ongoing Orders
>
> Jan             12                      23
> Febr            14                      26
> March           9                       19
> ....
>
> /Jerome
>
> Previous Posts In This Thread:
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> How to Run ASPNET_REGSQL on your Shared Hosting Sitehttp://www.eggheadcafe.com/tutorials/aspnet/c71593ff-b7ba-41ba-a0f9-e...

If the MDX solution does not work out, perhaps you might join a
calander to fact table. So rather than a record per contract, you
expand the fact table to the number of months that span the validdate
to and from dates: ie From: Jan To: Mar represents three records and
populate a count measure.

Good Luck

0 new messages