Skip to Content

Using the last element in a dimension

I am trying to build a chart that shows the number of orders by time period (in this case, calendar date). The data for sales orders contains multiple dates. I only want to reflect the latest (most current) date in the dataset. For example:

Order X was entered on May 1 with a delivery date set for June 1.

Order X was changed on May 2 to set the delivery date to June 2.

I want the chart to only show the delivery date of June 2. I have tried multiple versions of filtering, scripting, etc. and I am at a loss.

We are on Design Studio 1.6

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jun 09, 2017 at 10:03 AM

    Hi Jim,

    The end result you need for the chart display certainly makes sense from a business perspective, whereby you want to aggregate order counts by current delivery date. It seems to me that you have been provided with an inappropriate BEx Query for the purpose, which returns all changes to sales order delivery dates instead of just the current delivery date. I reiterate Tammy's recommendation that for this scenario the required filtering and aggregation should be performed in the BEx Query itself. As you've pointed out, if you don't have access to modify the query yourself, then the responsible BW Administrator or Developer should update the query or create a new one that meets the business requirement.

    Based on my understanding of your requirement, I don't think there is any combination of sorting, scripting or data binding/selection that will allow you to dynamically perform the needed filtering and aggregation client-side and then apply it to the chart. Using an appropriately defined BEx Query is the way to go.

    Regards,

    Mustafa.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 08, 2017 at 07:01 PM

    Tammy,

    Yes, the data is coming off of a BEx query view. I do not have access to query build to do any modifications to the query itself. We are on SP4.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 08, 2017 at 08:49 PM

    Hi Jim,

    Can you apply sort on Order in edit Initial View ? You can try to manipulate using Script to handle Sort either way and map first row as Data Binding -> 'Data Selection' under Chart component.

    Add comment
    10|10000 characters needed characters exceeded