How to reference a value (column) on the same row in a Set Analysis

[rawhite]

I am trying to write a report that needs input from a cell on the same row of a straight table in a Set Analysis.  My thoughts were to use the Country from the Budget table (presented in the Budget Report) in a set analysis that will derive a value for the Actuals by country / Synthetic Key.

sum({$<Budget.Synthetic_Key=Actual.Synthetic_Key
,Budget.Country={'Something Wise goes here'}>}
Actual.Actual_Sales)

I am attaching the data model, Table Values and desired report for examples.  I am in need of "Something Wise" 🙂

Thanks,

Rick

[Ksrinivasan]

hi,

To avoid circular reference,

Rename the Budget_Country as Budget_Country _Cus in customer table,

ksrinivasan

[marcus_sommer]

Just concatenate actuals and budget by ensuring that the fieldnames are equal - means no kind of qualifying - and adding an extra field Source which contains the appropriate string-values of 'actual' respectively 'budget'.

With this source-field you could now select which data should be displayed and/or you add it within a set analysis like:

sum({< Source = {'actual'}>} VALUE)
sum({< Source = {'budget'}>} VALUE)

to differentiate between them.

- Marcus