Difference between the values of max date and second max date

[jeeva]

Hi,

I am trying to create a report with 
1) the Max Date in a column, and
2) second highest date in the column.

My report has to compare the values of 1) and 2) above.

Example:

Date                Value
-------            --------
01/01/2003    100
05/01/2003     5
10/01/2003    250
12/01/2003    15
20/01/2003    150

From the above i can return the value for the max date by using MAX(Date)
which is 20/01/2003.
How do i return the second highest date i.e. 12/01/2003

Actually, I want to display the difference between the two values assigned to each of these dates. 

Thanks

[Lynn]

Hi,

I am trying to create a report with 
1) the Max Date in a column, and
2) second highest date in the column.

My report has to compare the values of 1) and 2) above.

Example:

Date                Value
-------            --------
01/01/2003    100
05/01/2003     5
10/01/2003    250
12/01/2003    15
20/01/2003    150

From the above i can return the value for the max date by using MAX(Date)
which is 20/01/2003.
How do i return the second highest date i.e. 12/01/2003

Actually, I want to display the difference between the two values assigned to each of these dates. 

Thanks

Take a look at this post to see if it gives you some ideas, specifically with regard to ranking as a means to identify first and second as well as julian dates and running difference to do the comparison.

[MFGF]

Hi,

I am trying to create a report with 
1) the Max Date in a column, and
2) second highest date in the column.

My report has to compare the values of 1) and 2) above.

Example:

Date                Value
-------            --------
01/01/2003    100
05/01/2003     5
10/01/2003    250
12/01/2003    15
20/01/2003    150

From the above i can return the value for the max date by using MAX(Date)
which is 20/01/2003.
How do i return the second highest date i.e. 12/01/2003

Actually, I want to display the difference between the two values assigned to each of these dates. 

Thanks

Are you using a dimensional or a relational package? The solution will be radically different for each.

I'm going to make a wild assumption that it's a relational package. As you already worked out, it's easy to get the maximum date using the maximum() summary in a query calculation
eg

maximum([Date query item] for report)

Getting the second-highest date is more of a challenge.

You could try the following approach:

1. Add a query calculation to introduce a running count: running-count ([Date])
2. Add a second calculation to figure out the maximum value of this (ie the value corresponding to the maximum date):  maximum([running-count] for report)
3. Add a third query calculation to return the date corresponding to the max -1 count value:  maximum(if ([running-count] = [MaxRunningCount] - 1) then ([Date]) else (null) for report)

This should allow you to have the second-highest date available. If you then want the measure values for the max date and this date, use separate query calculations with the approach

maximum(if ([Date] = [Maxdate]) then ([Value]) else (0))

maximum(if ([Date] = [SecondDate]) then ([Value]) else (0))

Cheers!

MF.

[MFGF]

Take a look at this post to see if it gives you some ideas, specifically with regard to ranking as a means to identify first and second as well as julian dates and running difference to do the comparison.

A much more elegant solution than mine! :)

[jeeva]

Are you using a dimensional or a relational package? The solution will be radically different for each.

I'm going to make a wild assumption that it's a relational package. As you already worked out, it's easy to get the maximum date using the maximum() summary in a query calculation
eg

maximum([Date query item] for report)

Getting the second-highest date is more of a challenge.

You could try the following approach:

1. Add a query calculation to introduce a running count: running-count ([Date])
2. Add a second calculation to figure out the maximum value of this (ie the value corresponding to the maximum date):  maximum([running-count] for report)
3. Add a third query calculation to return the date corresponding to the max -1 count value:  maximum(if ([running-count] = [MaxRunningCount] - 1) then ([Date]) else (null) for report)

This should allow you to have the second-highest date available. If you then want the measure values for the max date and this date, use separate query calculations with the approach

maximum(if ([Date] = [Maxdate]) then ([Value]) else (0))

maximum(if ([Date] = [SecondDate]) then ([Value]) else (0))

Cheers!

MF.

Hi,

Thanks for reply. we are using Relational package.
and we are getting max date and second max date with values as below
EmpNo       Date                Value
-------       --------          --------------
123       12/01/2003       15    (Second Max date and value)
123       20/01/2003      150   (Max date and value)

But, we need to find out the difference between values which assigned to each of these dates. for this i used running-difference(Date for EmpNo) as a query calculation. then it is giving the difference as below
EmpNo       Date                Value                  Diff_Sal
-------       --------          --------------        -------------
123       12/01/2003       15                                 
123       20/01/2003      150                         100

now, i applied filter for maximum date, it working fine. but diff_sal is showing empty as above..so how can we show the values as mentioned below. please suggest me.

EmpNo       Date                Value                  Diff_Sal
-------       --------          --------------        -------------
123           20/01/2003        150                         100

[cognostechie]

Not as good as Lynn and MF's solution but just another thought:

Query 1 - Create a column with function - maximum(Date)
Query 2 - Create a column with function - maximum(Date) and put a filter on this query like   Date <> maximum(Date). This will exclude the Highest Date from the query so the function will get the 2nd highest date.

Then join these two depending on what is required and the key and then do a datediff of both dates.
               

[MFGF]

...now, i applied filter for maximum date, it working fine. but diff_sal is showing empty as above...

Hi,

The sentence above is the crux of your issue. If you apply a filter for maximum date, you are excluding all other rows from your query (including the second max date), so this is no longer available for use in your calculation.

MF.

[jeeva]

Not as good as Lynn and MF's solution but just another thought:

Query 1 - Create a column with function - maximum(Date)
Query 2 - Create a column with function - maximum(Date) and put a filter on this query like   Date <> maximum(Date). This will exclude the Highest Date from the query so the function will get the 2nd highest date.

Then join these two depending on what is required and the key and then do a datediff of both dates.
               

Thanks for your reply. it is worked fine.