Find Max Date

[#1]

I have a table that looks like:

ConsolOrderNo	CustomerName	SalesOrderNumber	MaterialNumber	ConfirmDate	Sales
6478842	ABC	6478842	78301-002450	7/5/2012	56.16
6478842	ABC	6494876	14201-000165	7/9/2012	403.86
6478842	ABC	6496773	70661-016201	7/5/2012	5335.4
6478842	ABC	6496773	70661-016401	7/5/2012	8004.15
6478842	ABC	6497914	71610-026460	7/10/2012	1903.98
6484913	DEF	6482617	71680-015670	7/12/2012	44.64
6484913	DEF	6484913	70020-010418	7/10/2012	29.52
6484913	DEF	6484913	70020-009040	7/9/2012	18.72
6484913	DEF	6484913	70261-001299	7/12/2012	222.12
6484913	DEF	6492896	72000-018400	7/25/2012	31.68

I want to highlight (or make bold) the oldest date for each ConsolOrderDate. So for CosolOrderNo 6478842 I want the date 7/10/2012 in bold and or order 6484913 I want the date 7/25/2012 in bold.

How can I so this?

Thanks,

Stephen

[nagaiank · Accepted solution]

Attached qvw file has one solution.

[#3]

Krish,

I have your example. How did you get it to be bold?

Stephen

[#4]

Krish,

I have your example. How did you get it to be bold?

Stephen

[nagaiank]

In script, I get the latest ConfirmDate. In the table chart, I define the text format to bold when the ConfirmDate equals the latest date.

[#6]

You can also use an expression like this in the chart, Properties--> Expressions --> TextFormat :

f(ConfirmDate=max(total <ConsolOrderNo> ConfirmDate),'<B>')

Hope it helps

[#7]

Next Problem.

In my data above take ConsolOrderNo 6478842 and make the ConfirmDate 7/10.2012 for all MaterialNumber. Meaning that all Sales Orders and their detail lines for Consolidated Order number  6478842 has the same confirmed date. Because they all have the same confirm date, I do not want to show ConsolOrderNo 6478842 in a straight or pivot table. But I do want to show ConsolOrderNo 6484913 because all of the Confirm Dates are not the same.

In other words, I only what to show the user ConsolOrdersNo's where the dates are not all the same

How would I do this?

Thanks,

Stephen

[nagaiank]

I do not quite understand what you want to do.

For the data in the original post, if you indicate the result expected, we may be able to arrive at a procedure to get there.

[#9]

See attached, it should explain what I am looking for.

Thanks,

Stephen

[#10]

Using Krish method I created a selection criterial using:

Data3:

LOAD ConsolOrderNo,

if(Count(distinct `REQUEST_SHIP_DATE`)=1,'All Same Date','Multiple Dates') as CountDate

Resident OpenOrders Group By ConsolOrderNo;

Left Join (OpenOrders) LOAD * Resident Data3;

DROP Table Data3;

Stephen