Subtracting a Total measure from a measure based on Ranking!

building reports desktopintelligence
1

Hello,
I have a block that shows me the Top 3 accounts for each Manager (section by Manager) based on Matched items. I can show another block within the same section for the total as well i.e., without ranking. But what I want is a Residual i.e., the difference between Total of all the accounts for a Manager MINUS total of the top 3 accounts for the same manager. Matched items is the measure I am using to rank here.

E.g:

Manager = Mike

A001 200
A005 200
A123 140
Grand Total = 540
Total for Mike = 600
Residue = 600-540 = 60

I tried ForAll, but as you might be aware, when it is in the block where Ranking is applied, the Total also shows only the total of the Top 3 accounts based on Matched items i.e., ForAll shows only 540.

Is there a way to do this in BO DeskI 12.1? Pls help.

Thanks,
Prabhu.

prabhuram40 (BOB member since 2006-06-23)

2

Hi,

Will the NoFilter() function be useful for getting the value of “Total of all the accounts for a Manager”? Give it a try.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

It works like a charm. Thanks a lot :smiley:

prabhuram40 (BOB member since 2006-06-23)

4

Hi Marek

Where can i user or decler nofilter() funciton.
I am new to this BO, so realy i dont know where can i use this funcitons,
is it on filed or some where else please can you give me some detail informaton.

Thanks
Harika

harika.bo (BOB member since 2010-03-17)

5

Hi,

When a global filter or a table-level filter is applied on a table then to get a value of non-filtered object (its all values), use NoFilter() in a cell/column.

Do check the description of the function in the help.

Marek Chladny :slovakia: (BOB member since 2003-11-27)