ForceMerge

Quite a useful function I should say. I think it just might be the solution to much of the Multivalue errors I've been having in my job as a Reporting Officer.

Here's how ForceMerge works :

First, you make two queries. One contains the dimension you don't have in your second universe and a common field,the other one contains that common field and some calculated data.

The left table contains states and cities, the right one contains cities and Sales figures. What we would really want, is State and Sales figures.

So, we move the sales figures in with the left table ( first we merge the city object of course).

There.. looking great. Just, we don't want the city. we want the state. But when we remove it:

We get ourselves a cartesian product. The solution to this issue is: Forcemerge().

When we change the formula for the Sales figures to : =ForceMerge([Sales Revenue]), the following table emerges:

Ain't that a beauty :)

That's all folks.

Enjoy,

Peter