Web Intelligence Nofilter() function for Block data only

[Former Member]

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)?

[mhmohammed]

Hi Dave,

Few questions:

1. How are you getting last 24 months data in the report? What kind of filter are you using to get last 24 months data?
2. Would you be able to post a picture of table or chart what you want to see, with dummy data? That would help us to help you.
3. Do you want to create a flag for Dates for last 12 months and last 13-24 months? If yes, its easy to do that.

Thanks,

Mahboob Mohammed

[Former Member]

1. I have  universe filter applied to the query to limit to the last 24 months based on a prompt month and year.

2.  I can post a picture in a bit.  Essentially, I have created a cross tab with Year/month across the top, and the TTM Revenue amount as a measure. in the table (among other measures)

3.  I have created the T12 and T24 flags, but it does not change.  Runningsum will only calculate for the data that is in the report/block - when I filter to T12, any previous months no longer exist in the report.  For example - my month 12 months back shows a T12 amount = to the amount for that month because it is now the first month in the block/report.

When I use nofilter() the amount for the month 12-months back will work.  However, it will also ignore all of the input controls I have in place. 

[Former Member]

So your actual problem is if you use nofilter then it is also ignores input control

filters.

Refer .

Hope this could be helpful

Let me know if it works

[Former Member]

Hi Dave,

What I think is as you are using universe filter, the data is getting filter at the query level itself and not getting fetched in the SQL raw data; hence Previous( ) is failing.

With drilling it is working fine because the SQL is generated as per the scope of analysis set and fetches the data for set hierarchy.

Also, NoFilter( ) will work properly in case of report filters and not query filters.

I would suggest you to put report filter instead of query filter and check.

Regards,

Yuvraj