Challenge issue - Runningsum with limited range

38 Views

Hi There,

I am trying to count total incidents in every 90 days and select those IDs have total incidents > 10 during 90 days. Here attched the corsstab report I created for your reference.

YearMonth

ID 201301 Sum 201302 Sum 2013-03 Sum ..... 201601 Sum

A 1 1 2 3 3 6 3 64

B 2 2 4 6 1 7 1 59

C ......

I tried to use:

Column"YearMonth" =FormatDate([MyDate];"yyyy-MM")

Column"Sum"=RunningSum(Count([Incident Number]);Row;([ID]))

but it would return the runningsum for alll months, how can I limit the runningsum to only sum the last 90days/3months?

For example:

Show sun of 201302,201303,201304 at RunningSun after 201304

Show sun of 201303,201304,201305 at RunningSun after 201305

I also tried to use but not works:

Column"Sum"=RunningSum(Count([Incident Number]);Row;([ID])) Where ([MyDate]>RelativeDate([MyDate];-90) AND ([MyDate]<=RelativeDate([MyDate];+1))

I wish to achive this using functions, filters directly in WeBI, not create Universe or tables, is it doable?

Thanks,

Matthew