Skip to Content
author's profile photo Former Member
0
Former Member

Problem with summating key figures with a condition

Posted on Feb 17, 2005 at 02:01 AM 11 Views

Hi,

I would to achieve a query that reports Internal Orders that have a YTD Actual of less than $1 million.

The problem is that I wish just show these "minor projects" at an aggregated level by month (defined as a row). I am defining this for the purpose of graph.

1. I defined a condition that restricts my Orders Actual key figure to less than $1m and evaluates the condition on the Order.

2. I have defined my Rows as Month. (Note that Order is not part of the Row definition - it has been entered as a free characteristic.

3. My Actual Key Figure column has defined in its properties that the Result should be calculated as a "Summation".

My problem: For example, I have one month which has one project of value $1.1 million and one project of value $200 000.

- I would expect the total value to be displayed as $200 000. Instead it shows a value of $0.

If however, I define my Rows as Month / Order (moving Order from Free characteristic to Row), I can see that the condition is correctly excluding the value of $1.1 million and infact will show the correct total as $200 000.

-Unfortunately showing this detail will not work for the definition of my graph.

It seems that despite my conditon evaluation being based on the Order, it is infact basing it on the Month.

Many thanks

Adrian

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Tom Francis
    Tom Francis
    Posted on Feb 17, 2005 at 08:26 AM
    0

    Did you try changing the settings in the condition from "All characteristics independently to" --> "Single characteristic or one by one"

    Tom

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Feb 21, 2005 at 06:41 AM

      Hi Tom,

      Yes my condition evaluated for the single characteristic of 'Order'.

      Further to my previous mail, I have created another example and the total shown is $1.1 million (total of all orders; not zero) - thus the query is overlooking the condition all together when the characteristic it is not defined as a Row.

      I've also tried a number of other ways to calculate the total of Orders of value less than 1 million but still no luck. FYI:

      Failed attempt #2: Defined a new single-level hierarchy for all orders. I then defined my Rows as Month + Order(reporting to Level 1 of the hierarchy). Unfortunately 'Calculated as Summation' is not allowed with hierarchies.

      Failed attempt #3: Defined a calculated key figure that turned actual values of greater than 1 million to zero i.e.

      (Actual < $1000 000) * Actual.

      In this case, it correctly showed values for orders of less than $1000 000 and zero for orders exceeding the limit when I defined my Rows as Month followed by Order. However once I moved Order to a Free Characteristic, it showed a total of $0 for the month. Thus the calculation is occuring at the aggregated level rather than by Order. This does not make sense to me either.

      I'm sure there must be some way of showing the total of orders with values less than $1 million but am rapidly running out of ideas.

      Thanks

      Adrian

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.