topic Re: Find Max Date in QlikView

Find Max Date

Fri, 06 Jul 2012 19:15:29 GMT

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

Re: Find Max Date

nagaiank — Fri, 06 Jul 2012 20:22:57 GMT

Attached qvw file has one solution.

Re: Find Max Date

Fri, 06 Jul 2012 20:23:03 GMT

Krish,

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

Stephen

Re: Find Max Date

Fri, 06 Jul 2012 20:23:18 GMT

Krish,

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

Stephen

Re: Find Max Date

nagaiank — Fri, 06 Jul 2012 20:27:24 GMT

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.

Re: Find Max Date

Sat, 07 Jul 2012 14:03:35 GMT

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

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

Hope it helps

Re: Find Max Date

Wed, 11 Jul 2012 23:55:19 GMT

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

Re: Find Max Date

nagaiank — Thu, 12 Jul 2012 00:11:07 GMT

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.

Re: Find Max Date

Thu, 12 Jul 2012 14:29:56 GMT

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

Thanks,

Stephen

Re: Find Max Date

Thu, 12 Jul 2012 21:24:12 GMT

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

Re: Find Max Date

nagaiank — Thu, 12 Jul 2012 23:33:36 GMT

Stephen,

Please change the expresion from

Sum(OrderQty)

If(Count(Distinct TOTAL <ConsOrderNo> ConfirmDate) > 1, Sum (OrderQty), Null())

and see if you get the result you want.

Re: Find Max Date

Thu, 12 Jul 2012 23:58:09 GMT

Your the man! Thanks Krish. Works perfect.

Stephen