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