COGNOiSe.com - The IBM Cognos Community

Hello,

In my report i applied Group by on Segment, Job and Sub Job Number.

I want to extract max and min date for each Segment/job/sub by their group.

For example in the below sample data I want to get the min date as May 3rd and Max Date as May 21st for Sub 01 and May 13th -June 4th for Sub 09

Can you please help us with a formula to extract max and min dates.


Job No      Sub   SegmentDesc   Week   Min                   Max                  Duration   Date Worked   Start Date           End Date
                           
ACMV00   01    Supervision   Monday   May 3, 2021   May 21, 2021    18.00    May 3, 2021   May 3, 2021   May 3, 2021
ACMV00   01    Supervision   Friday                                                    May 21, 2021   May 21, 2021   May 21, 2021

ACMV00   09    Supervision   Thursday   May 13, 2021   Jun 4, 2021    22.00    May 13, 2021   May 13, 2021   May 13, 2021
ACMV00   09    Supervision   Friday                                                    Jun 4, 2021   Jun 4, 2021   Jun 4, 2021

Thanks in advance
Raju

Quote from: rajua99 on 14 Jun 2021 04:56:36 PM
Hello,

In my report i applied Group by on Segment, Job and Sub Job Number.

I want to extract max and min date for each Segment/job/sub by their group.

For example in the below sample data I want to get the min date as May 3rd and Max Date as May 21st for Sub 01 and May 13th -June 4th for Sub 09

Can you please help us with a formula to extract max and min dates.


Job No      Sub   SegmentDesc   Week   Min                   Max                  Duration   Date Worked   Start Date           End Date
                           
ACMV00   01    Supervision   Monday   May 3, 2021   May 21, 2021    18.00    May 3, 2021   May 3, 2021   May 3, 2021
ACMV00   01    Supervision   Friday                                                    May 21, 2021   May 21, 2021   May 21, 2021

ACMV00   09    Supervision   Thursday   May 13, 2021   Jun 4, 2021    22.00    May 13, 2021   May 13, 2021   May 13, 2021
ACMV00   09    Supervision   Friday                                                    Jun 4, 2021   Jun 4, 2021   Jun 4, 2021

Thanks in advance
Raju

Hi,

Which item specifically do you need to find the max and min values for? You appear to have three date items - will they always be the same?

Assuming it's Date Worked, and also assuming you need to take account of both the Job No and the Sub items for the maximum, you can write expressions in query calculations. Max value would be

maximum([Date Worked] for [Job No],<sub>)

Min value would be the same but using minimum() instead of maximum()

Cheers!

MF.</sub>