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
Quote from: Thanay on 16 Oct 2015 07:39:24 AM
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 (http://www.cognoise.com/index.php/topic,29257.0.html) 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.
Quote from: Thanay on 16 Oct 2015 07:39:24 AM
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.
Quote from: Lynn on 16 Oct 2015 07:51:49 AM
Take a look at this post (http://www.cognoise.com/index.php/topic,29257.0.html) 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! :)
Quote from: MFGF on 16 Oct 2015 08:14:13 AM
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
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.
Quote from: Thanay on 17 Oct 2015 10:04:33 AM
...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.
Quote from: cognostechie on 18 Oct 2015 02:30:16 PM
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.