I receive data from our ERP system on vouchers that create errors. I want to have a measure that tells me how many voucher lines were closed within the last week. But... a voucher can create multiple errors - one might be closed during that week, but if the same line has an open error I don't want to count that toward the closed line total.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Voucher Key[/TD]
[TD]First Date[/TD]
[TD]Last Date[/TD]
[TD]Status[/TD]
[TD]Error[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POG_0556221_1[/TD]
[TD]11/7/19[/TD]
[TD]11/9/19[/TD]
[TD]Closed[/TD]
[TD]P400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POG_0556221_1[/TD]
[TD]11/7/19[/TD]
[TD]11/11/19[/TD]
[TD]Open[/TD]
[TD]R500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POG_0558442_2[/TD]
[TD]11/8/19[/TD]
[TD]11/10/19[/TD]
[TD]Closed[/TD]
[TD]E110[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As of 11/12/19 these 3 lines would be part of a closed voucher line report.
In this example voucher 0556221 line 1 spun two errors, one of which is closed. All 3 records, 2 voucher lines, have a closed status within the last week, but because 0556221 line 1 still has an open error I don't want to count it as closed for the weekly status. The total of closed lines = 1 for voucher 0558442.
What is the best way to create this measure? I have little experience with UNION and INTERSECT. Perhaps SUMMARIZE based on the voucher key and status for the prior week, getting all voucher line counts then subtracting the count where status = Open?
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Voucher Key[/TD]
[TD]First Date[/TD]
[TD]Last Date[/TD]
[TD]Status[/TD]
[TD]Error[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POG_0556221_1[/TD]
[TD]11/7/19[/TD]
[TD]11/9/19[/TD]
[TD]Closed[/TD]
[TD]P400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POG_0556221_1[/TD]
[TD]11/7/19[/TD]
[TD]11/11/19[/TD]
[TD]Open[/TD]
[TD]R500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POG_0558442_2[/TD]
[TD]11/8/19[/TD]
[TD]11/10/19[/TD]
[TD]Closed[/TD]
[TD]E110[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As of 11/12/19 these 3 lines would be part of a closed voucher line report.
In this example voucher 0556221 line 1 spun two errors, one of which is closed. All 3 records, 2 voucher lines, have a closed status within the last week, but because 0556221 line 1 still has an open error I don't want to count it as closed for the weekly status. The total of closed lines = 1 for voucher 0558442.
What is the best way to create this measure? I have little experience with UNION and INTERSECT. Perhaps SUMMARIZE based on the voucher key and status for the prior week, getting all voucher line counts then subtracting the count where status = Open?