Filter Help

building reports desktopintelligence
1

Hi All,

kindly consider the following.

I have one crosstable in the report. 1 table level filter and 1 global level filter is applied to restrict the values in the table.

Ex. lets say global level filter is India. (to check for the total business in India)

Table level filter is state. ( to check for the total business done in the few states)

I am using 1 dimension “State” to check the state wise data.(which is a variable created based on zone) now i am restricting few states to be present in table. Lets say A, B, C out of all states. (this is my table level filter- states->A,B,C; i am not selecting other states)

Now i want to add 1 column in this crosstab which will give me the % of the corresponding state business comparing with all total states.(A+B+C/A+B+C+D+E+F+G)

So question is is there any function (similar to NoFilter) available so that i can vanish only table level filter for only 1 column.

Considering the maintenance part, i dont want to use where clause in this column.

i used ForAll states, but it is always showing me 100% value for each state, which is wrong.

Kindly suggest how to proceed.

:wah: :wah:

bryanket :bahrain: (BOB member since 2009-10-22)

2

Hi,
You need to use the NoFilter function to calculate the total value for all states. As said you need to calculate the sum of filtered states/Sum of all states. Try this Sum()/NoFilter(Sum())

So why you want a another similar function when you can do it using NoFilter().

-Thanks
Img1.jpg

forgotUN (BOB member since 2006-12-13)

3

Thanks for the help…

But as per what i said, i just want to remove table level filter and not global level filter.

As per the formula provided by you, it’s removing global level filter(i.e.india) due to NoFilter.

for ex. in your screenshot.

India has total 1500. Lets say there are multiple countries available in Object Country.
US - 3000
Aus - 7000

So my global level filter is Country = India.

Whenever i use NoFilter, it gives value = 900/(1500+3000+7000) and not
900/(1500).

:cry: :!:

bryanket :bahrain: (BOB member since 2009-10-22)

4

Hi,
What i observerd is, if you implement it as Crosstab then Nofillter function is not considering the global filter.
Looks strange…Have a try.
img3.jpg
img2.jpg

forgotUN (BOB member since 2006-12-13)