Skip to Content
0

Using the last element in a dimension

Jun 08, 2017 at 06:05 PM

60

avatar image
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

10 |10000 characters needed characters left characters exceeded

Hi Jim - is your data source a BEx query? It would be easier to default those items in BEx based on standard variables.

Which SP of Design Studio are you using?

0

Tammy, please find Jim's response as an answer blow.

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Mustafa Bensan Jun 09, 2017 at 10:03 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Jim Williams Jun 08, 2017 at 07:01 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Jim please "comment/reply" directly on the post you wish to respond to. Answers on your question will only send notifications to you.

0
Mohd Fahad Jun 08, 2017 at 08:49 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded