Input and Output calculation contexts Part 2

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.

pic10.1

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.

pic10.2

pic10.3

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

pic10.4

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s