Calculated member - conditional sum

[cubes]

Hi,

considering two dates:
the actual finish date and
the deadline;
and the dimension "id" (order id)

Calculation to do > SUM of all id's
prerequisite: actual finish date <= deadline

Which value expression do I need to define in the "calculated member builder"?
Shall I define the dates as measures or as dimension?

Thank you for giving feedback.
Regards,
Gaston

 

[justins57]

Gaston,

I think what you want to do is only sum the orders (IDs) where the actual finish date <= deadline date. In other words, don't include orders (IDs) that happen after the deadline. Is this correct?

The calculation is a little confusing - do you mean you want to sum the number of orders (IDs) or sum a measure for the total number of orders? Do the orders have a date attribute associated with them? Like a bill date or a ship date?

I think this would be easy to do if you can give more details about the structure of the cube and dimensions.

Justin

 

[cubes]

Justin,

Sorry for this confusion.
Your first statement is correct: count the number of order id's where the actual finish date <= deadline date.

Afterwards, I can calculate the following ratio:
(Nbr of Id's where the actual finish date <= deadline date)/ (total nbr of Id's)
This ratio is relevant in the context of SLA's (Service Level Agreements).

This measure is defined as a KPI in a scorecard and available in Sharepoint Portal.

I have "a" solution available using a virtual cube but this is a very limited solution because "drill through" actions are no longer possible. That's why I want all this information in one cube (the same data population).

Many thanks for your feedback!
Best regards,
Gaston