Help with a set analysis statement that allows me to identify a max value and then pull data related to that value

Mon, 27 Jun 2016 01:37:47 GMT

History table (records related to a claim):　

HistoryID | ClaimID | EventDate | Segment | ClaimOpen　

----------+---------+------------+---------+----------　

1 | 1 | 2016-01-01 | Level 3 | Y　

2 | 1 | 2016-01-02 | Level 3 | Y　

3 | 1 | 2016-01-03 | Level 1 | Y　

4 | 1 | 2016-02-15 | Level 1 | N　

5 | 2 | 2016-01-01 | Level 0 | Y　

6 | 2 | 2016-01-01 | Level 1 | Y　

7 | 2 | 2016-01-02 | Level 3 | Y　

Claim table:　

ClaimID | Adjuster　

--------+------------　

1 | Smith, Joe　

2 | Jane, Mary　

The script will set an event date range, where 　

let vEndDate = "2016-01-31";　

I need to be able to count all ClaimIDs where the Max EventDate in the History table, relative to the date 　

range (vEndDate), is on a row where the Segment = Level 2, Level 3 or Level 4 and a ClaimOpen value = Y. 　

Since I may have duplicate EventDates, I need to interrogate the HistoryID, since the largest HistoryID value 　

corresponds to the most recent EventDate, per ClaimID. Once I have identified that HistoryID, I then only 　

want to include/count the rows that meet the above 2　

conditions. 　

This is the expression I built:　

Count({　

<EventDate={"<=$(=vEndDate)"}　

,Segment={'Level 2','Level 3','Level 4'}　

,HistoryID={"=aggr(max(HistoryID), ClaimID)"}　

,ClaimOpen={"Y"}　

} distinct ClaimID)　

For this example, I need the expression to identify rows 3 and 7 as the rows with the max history for 　

their respective claim IDs, but only count row 7 since that is the only max row that meets the Segment and ClaimOpen conditions.

Re: Help with a set analysis statement that allows me to identify a max value and then pull data related to that value

Mon, 27 Jun 2016 16:42:16 GMT

I think I have solved part of the problem - I have figured out how to bring back the max historyID per claimID using FirstSortedValue. Even if the historyID's share the same date it is bringing back the max HistoryID value. Here is the syntax and it is working:

=Max(aggr(firstsortedvalue (HistoryID, -EventDate), ClaimID))

So now what I have to do is, for the max HistoryID value, count it if the HistorySegment and HistoryClaimOpen values meet my parameters. I am trying to use a set analysis expression to set the HistoryID to the max, but Qlik doesn't like it. Can I do this? And if so, how should it be wrttien?

Count ({
<HistoryID={Max(aggr(firstsortedvalue (HistoryID, -EventDate), ClaimNumber))}
,HistorySegment={'Level 2', 'Level 3', "Level 4'}
,HistoryClaimOpen = {'Y'}>
}distinct ClaimID)

topic Help with a set analysis statement that allows me to identify a max value and then pull data related to that value in QlikView

Help with a set analysis statement that allows me to identify a max value and then pull data related to that value

Re: Help with a set analysis statement that allows me to identify a max value and then pull data related to that value