Announcements
cancel
Showing results for
Did you mean:
Partner - Contributor II

## Trying to get Min/Max Dates based of another field

Hi I'm wondering if someone could help me please, I am trying to get a min/max date based of another fields so in the below table:-

 Status From Date To Date Days N 30/05/2019 31/05/2019 1 N 01/06/2019 03/06/2019 2 N 04/06/2019 04/06/2019 0 N 05/06/2019 29/01/2020 238 Y 30/01/2020 24/03/2020 54 Y 25/03/2020 15/06/2020 82 Y 25/03/2020 25/03/2020 0 N 16/06/2020 27/10/2020 133

You can see a list of dates a status has been in N or Y along with the number of days that status has lasted.

I need to be able to summarise this to the following:-

 Status From Date To Date Days N 30/05/2019 29/01/2020 241 Y 30/01/2020 15/06/2020 136 N 16/06/2020 27/10/2020 133

The issue I am facing is that as soon as i try to Min/Max the dates its giving me the following:-

 Status From Date To Date Days N 30/05/2019 27/10/2020 516 Y 30/01/2020 15/06/2020 136

This is incorrect at the N result needs to have 2 entries either side of the Y and I am coming up with a blank as to how to do this.

Any suggestions/solutions would be very gladly appreciated.

Many thanks

1 Solution

Accepted Solutions

@DJHodgson  you can always do this

in Script:

``````Input:

Status, From Date, To Date, Days
N, 30/05/2019, 31/05/2019, 1
N, 01/06/2019, 03/06/2019, 2
N, 04/06/2019, 04/06/2019, 0
N, 05/06/2019, 29/01/2020, 238
Y, 30/01/2020, 24/03/2020, 54
Y, 25/03/2020, 15/06/2020, 82
Y, 25/03/2020, 25/03/2020, 0
N, 16/06/2020, 27/10/2020, 133
];

final:
noconcatenate

load * resident Input order by [From Date];

drop table Input;``````

and in chart

dimension: Row ,Status

Measure
=Date(min([From Date]))

and

=Date(Max([To Date]))

output:

in qlikview we can hide some column then you can get

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
2 Replies

@DJHodgson  you can always do this

in Script:

``````Input:

Status, From Date, To Date, Days
N, 30/05/2019, 31/05/2019, 1
N, 01/06/2019, 03/06/2019, 2
N, 04/06/2019, 04/06/2019, 0
N, 05/06/2019, 29/01/2020, 238
Y, 30/01/2020, 24/03/2020, 54
Y, 25/03/2020, 15/06/2020, 82
Y, 25/03/2020, 25/03/2020, 0
N, 16/06/2020, 27/10/2020, 133
];

final:
noconcatenate

load * resident Input order by [From Date];

drop table Input;``````

and in chart

dimension: Row ,Status

Measure
=Date(min([From Date]))

and

=Date(Max([To Date]))

output:

in qlikview we can hide some column then you can get

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner - Contributor II
Author

sorry for the delay yes this worked perfectly for me thank you