Selecting the latest Order revision

Mr H

Summary

Need to retrieve the latest Orders revisions

Content

Hi,

  While creating an Analysis to retrieve the Sales Orders details we need to show only the latest revision of the Order and ignore the old ones.

Is there a function to be used or filter logic to achieve that?

Below a sample of the analysis created and we need to show only the records for Revision 4.0

 

The Subject Areas used are:
Order Management - Order Headers Real Time
Order Management - Order Lines Real Time

BR
Hany

 

Version

19d

ravikiran goda-Oracle

Hi Hany,

As per the screenshot how to identify which is the latest record ,because the Order line column is also populated with 2 values 1.0,2.0

 

Regards,

Ravi

Mr H

Hi Ravi, We need to set the Revision column = max(Revision) for that Order.
So for each Order we need to display only the records linked to the most recent Revision. For the example above it should be revision 4 or other Orders it could be 1 or 3 depending on the number of revisions.

Can we do that? How?

BR
Hany

Mr H

Hi Ravi. Did you have the chance to check?

Hamish Vickers

Hi,

Have you tried using one of the aggregate functions such as "MAX" ?
As you only want the data for the most recent, aka, the highest or MAX revision number?

Hamish

IIGC

Where you able to find a solution for this?

Mr H

Hi IIGC, unfortunately not yet.

Sahithi Kolasani-Oracle

Hi Mr H

Please create a hidden column called Rank and apply logic given below. Please change the column names as per your analysis.

Rank column logic

rank("- Inventory"."Item Revision Control" desc by "Orchestration Order Lines Details"."Order Number" )

After that, please apply a filter on the Rank = 1

With this solution, you should be able to get the latest release of a given order.

Attaching the xml here with. Please create a new analysis-> select the subject area and go to advanced tab.

Select all the content of the xml and paste the content given in the attachment. Click on apply.

Please check if this helps.

rank.txt

Thank you!

Andrew_Schmitz

I think we have a shared filter we use on most of our reports on Orders data.

User_259TP

Creating the rank column and then a filter to select rank = 1 worked perfectly for my application! I was trying to select the latest revision for each part number in a report. Thank you so much!

User_259TP

Now if I could just figure out how to show my display name in the comments. It defaults to my user name.