Abdul Qadeer
Abdul Qadeer

Reputation: 33

BIRT Expression to Filter Data Based on Consecutive Instances

I'm working on a BIRT report and need help refining a data filtering expression.

Problem:

I have an existing BIRT template (.rptdesign) and sample XML data. I've removed the first two columns from the report. The goal is to display only rows where a specific value occurs 7 or more consecutive times.

enter image description here enter image description here enter image description here enter image description here

Attempts:

I've created a BIRT expression but it's not producing the desired results.

Computed Column Name is maxConsecutiveDays, data type is Integer

var consecutiveDays = 0;
var maxConsecutiveDays = 0;
var lastDate = null;

for (var i = 0; i < dataSetRow["CalendarDate"].length; i++) {
    var currentDate = new Date(dataSetRow["CalendarDate"][i]);
    
    if (lastDate) {
        var diffDays = (currentDate - lastDate) / (1000 * 60 * 60 * 24);
        
        if (diffDays === 1) {
            consecutiveDays++;
        } else {
            if (consecutiveDays > maxConsecutiveDays) {
                maxConsecutiveDays = consecutiveDays;
            }
            consecutiveDays = 1;
        }
    } else {
        consecutiveDays = 1;
    }
    
    lastDate = currentDate;
}

if (consecutiveDays > maxConsecutiveDays) {
    maxConsecutiveDays = consecutiveDays;
}

maxConsecutiveDays;

Upvotes: 1

Views: 39

Answers (0)

Related Questions