Rolling 4 weeks Expression based on condition

[#1]

I need to provide a trend for open tickets per fiscal week. The definition of open per given fiscal week is that tickets created per give week is not closed at same week, also for tickets created prior to given week, if they are not closed by given week, it will also be counted as open per that week.

For example, based on the sample data under model:

Sample:

load * Inline [

Record No,CreatedFW,ClosedFW,Flag

1,111,113,1  

2,111,111,1

3,112,114,1

4,112,116,1

5,113,117,1

6,113,113,1

7,113,119,1

8,114,115,1

];

The count for each fiscal FW should be as follows:

111 - 1    (Record No: 1 )

112 - 3    (Record No: 3,4 + Record No: 1 )  //note, the reason Record No: 1 needs to be counted due to its close date > given fiscal                week (112)

113 - 4    (Record No: 5, 6  + Record No: 3, 4) //note: Record No: 5, 6 are those created current fiscal week, 113 with closed ate > 113.                while Record No: 3, 4 are created previously but not closed per 113 fiscal week

114 - 4    (Recod No: 8 + Record No: 5, 7 + Record No: 4)

In SQL, the query for the given fiscal week such as 114 will be as follows:

SELECT *

FROM Sample

where createdFW <=114

and ClosedFW > 114

What's the best way to handle this one in expression? Attached is my sample qvw.

Please kindly advise!

[jerem1234]

Hi Linling,

I would start by creating a FiscalWeek data island that just lists the weeks you want. I added this to your script:

Load * Inline [

FiscalWeek

111

112

113

114

];

Then I used this formula with dimension FiscalWeek:

count(if(ClosedFW > FiscalWeek and CreatedFW<=FiscalWeek, CreatedFW))

This will give you the numbers you are looking for. Also changed the listbox to FiscalWeek.

PFA

Hope this helps!

[#3]

You are super awesome! It works like a magic...

[jerem1234 · Accepted solution]

Could you mark this discussion as correct/helpful, in case others might find this useful?

Thanks!