Challenge issue - Runningsum with limited range

[Former Member]

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

[Former Member · Accepted solution]

Hi,

Try [sum 90 days]= RunningSum([incident number])-RunningSum(Previous[incident number];3))

Regards,

Rogerio

[Former Member · Accepted solution]

Hi Rogerio,

Thank you so much for providing me such a simple solution! I never use Previous function before and it is exactly what I need!

I believe this would help others who are not familiar with RunningSum() and Previous() functions.

Thanks again!

Matthew

[Former Member · Accepted solution]

Hi Matthew, I'm glad it worked.

Regards,

Rogerio