In Part 1 of this post I explained what Input and Output calculation contexts are. Now let’s focused on some advanced solutions. How about a combination of a calculation context and a filter with a rank function?

Here is a sample data set from eFashion database. Let’s say we want to compare total Sales revenue with Sales revenue of top 3 lines in each year and put them next to each other.

On the left we have a simple table with 3 columns and on the right is the same table but with a break applied on a Year and a rank on Sales revenue. There is also a subtotal calculation for each Year and this is what we want to display in a table with only Year in a table context.

We can accomplish this by simply applying a rank on a table. This would work as a filter on a table so to get an additional column with total Sales revenue we need to use NoFilter() function.

Now let’s do it without applying a rank as a filter on a table and use only formulas. I will use the following formula for Top 3 revenues:

=Sum( [Sales revenue]
Where( Rank([Sales revenue];([Lines]);Top;[Year])<4)
In([Lines];[Year])
)

What’s interesting is that in Rich Client I will get correct results but if I open the same report in Infoview they’re incorrect.

Infoview vs Rich Client

To make formula working in both places we need to change it a little bit:

=Sum(
If(Rank( ([Sales revenue] In([Lines];[Year]));([Lines]);Top;[Year])<4;
[Sales revenue];0)
In ([Lines];[Year])
)

### Like this:

Like Loading...

*Related*