flagging correct row in a group with date logic

[scabral]

Hi,

so this one is a little tricky but i have a table with the below columns:

ClaimId	LocationId	ReserveDate	AccuracyInd
1111	99	1/1/2021	1
1111	99	1/16/2021	1
1111	99	4/1/2021	1
1111	99	4/4/2021	1
1111	77	6/1/2021	1
1111	77	6/6/2021	0
1111	77	7/1/2021	0
1111	77	7/3/2021	0
2222	33	3/3/2021	0
2222	33	4/2/2021	0
2222	33	5/1/2021	1
2222	33	5/6/2021	0
2222	33	8/1/2021	1
4444	88	6/6/2021	0
4444	88	7/4/2021	0
4444	88	7/9/2021	1
5555	76	8/1/2021	0
5555	76	8/8/2021	0

For each ClaimId and LocationID group, i need to create a new calculated column (called GroupAccuracyInd) that will be either a 1 for the first row that became accurate (AccuracyInd = 1) after the max inaccurate row (AccuracyInd = 0).  So for the first group above (ClaimId 1111 LocationId 99) all rows are accurate so i will select the row with Reserve Date of 1/1/2021.  For the second group (ClaimId 1111 LocationId 77) I will need to flag the 6/1/2021 row since that was the first row that was accurate and no others.  For the third group (ClaimId 2222 LocationId 33) it becomes a little tricky.  The first row to become accurate is on 5/1/2021 but then it becomes inaccurate on 5/6/2021 and then accurate again on 8/1/2021.  I will need to flag the 8/1/2021 row since it's the first accurate row after the max inaccurate row for the group.  For the last group (ClaimId 5555 LocationId 76) there are no accurate rows so they will just default to 0.

so the final field should look like this:

ClaimId	LocationId	ReserveDate	AccuracyInd	GroupAccuracyInd
1111	99	1/1/2021	1	1
1111	99	1/16/2021	1	0
1111	99	4/1/2021	1	0
1111	99	4/4/2021	1	0
1111	77	6/1/2021	1	1
1111	77	6/6/2021	0	0
1111	77	7/1/2021	0	0
1111	77	7/3/2021	0	0
2222	33	3/3/2021	0	0
2222	33	4/2/2021	0	0
2222	33	5/1/2021	1	0
2222	33	5/6/2021	0	0
2222	33	8/1/2021	1	1
4444	88	6/6/2021	0	0
4444	88	7/4/2021	0	0
4444	88	7/9/2021	1	1
5555	76	8/1/2021	0	0
5555	76	8/8/2021	0	0

Thanks

Scott

[Nathaniel_C]

Hi @scabral you are welcome!
Nathaniel

[Nathaniel_C]

Hi @scabral , Try this:

AccGroup =
VAR _claimId =
    MAX ( myTable[ClaimId] )
VAR _locationID =
    MAX ( myTable[LocationId] )
VAR _curDate =
    MAX ( myTable[ReserveDate] )
VAR _minDate =
    CALCULATE (
        MIN ( myTable[ReserveDate] ),
        FILTER (
            ALL ( myTable ),
            myTable[ClaimId] = _claimId
                && myTable[LocationId] = _locationID
                && myTable[AccuracyInd] = 1
        )
    )
VAR _isMin =
    IF ( _minDate = _curDate, _minDate )
RETURN
    _isMin

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

and this:

AccGroupFinal = 
VAR _claimId =
    MAX ( myTable[ClaimId] )
VAR _locationID =
    MAX ( myTable[LocationId] )
VAR _curDate =
    MAX ( myTable[ReserveDate] )

VAR _minDate =
    CALCULATE (
        MIN ( myTable[ReserveDate] ),
        FILTER (
            ALL ( myTable ),
            myTable[ClaimId] = _claimId
                && myTable[LocationId] = _locationID
                && myTable[AccuracyInd] = 1
        )
    )

Var _maxDateWithZero = CALCULATE (
        MAX(  myTable[ReserveDate] ),
        FILTER (
            ALL ( myTable ),
            myTable[ClaimId] = _claimId
                && myTable[LocationId] = _locationID
                && myTable[AccuracyInd] = 0))   //gets the max date with zero 

Var _choose= CALCULATE(MIN(myTable[ReserveDate]),FILTER(All(myTable),
            myTable[ClaimId] = _claimId
                && myTable[LocationId] = _locationID
                && myTable[AccuracyInd] = 1
                && myTable[ReserveDate]>_maxDateWithZero))


VAR _isMin =
    IF ( _minDate = _curDate, _minDate )//this displays blanks

Var _whichOne= IF(_maxDateWithZero>_choose,_minDate,_choose)
RETURN
  
 // if(_whichOne = _curDate,_whichOne)

 IF(_whichOne=_curDate,1,0)

Which gives us this:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[scabral]

Hi Nathaniel_C,

this seems to work well.  I only had to remove the MAX from teh initial variables and then it seems to be correct.

Thanks for your help!