Skip to Content
0
Former Member
Mar 30, 2016 at 01:34 PM

Web Intelligence Nofilter() function for Block data only

1361 Views

I am generating a number of reports that will require trailing 12 month calculations, presented in a trend report by month. In summary, for each of the last 12 months, I need to display the prior 12 months Revenue. I have brought the data into my query and I can very simply calculate Trailing Twelve month figures using

Runningsum([Amount_Rev]) - Runningsum(previous([Amount_Rev],12))

as long as I do not filter out the prior 12 months from my report.

At the end of the day, we do not want to display the last 24 months, so I will filter the block/report down to only show the last twelve. Heres the rub - I can apply the T12 filter to the block, but then my rolling running sum calculations will not be able to reference the data from month -13 to -24.

I have a work around - I have used a drillfilter to apply my T12 filter, and then in my calculation I have modified it to say:

Nofilter(Runningsum([Amount_Rev])-Runningsum(previous([Amount_Rev];12));Drill)

That seems to work, but I don't like using the drill filter for this purpose. We likely will turn on drill, and I do not want the users to be able to manipulate the T12 filter when looking at the filter bar.

Is there a way to use a formula similar to Nofilter([Measure],Block)?