Referencing other Dimensions

[#1]

I have a chart table that aggregates to the below:

dim       | BV     | BY     | custom

Begin    | 50      | 2

1          | 20      | 1.5

2          | 15      | 4.8

3          | -10     | 3.2

End      | 75      | 3

dim is a dimension with those values;

BV is sum(BV)

BY is sum(BY*BV)/sum(BV) **Weighted average aggregation

The custom field requires special logic:

     ([Begin BV] * BY / BV)

so the calculation in custom for the dim Begin would be: (50 * 2 / 50)

the calculation for dim=1 would be: (50 * 1.5 / 20)

the calculation for dim=2 would be: (50 * 4.8 / 15)

etc...

Anytime I try to get some sort of set analysis to grab the 'Begin BV' it only returns it in the dim=Begin, I can't seem to access this data from a different dimension.  I've thought of pre-calculating some of these values in the load, but the Begin and End are two dates that the User selects at run-time.  Any thoughts/ideas how I can accomplish this calculation?

[swuehl · Accepted solution]

Add a TOTAL qualifier to your aggregation function with the set analysis, e.g.

sum({<dim={Begin}>} TOTAL BV)

for the [Begin BV] calculation

[#3]

Hi Mark, a few ways to do this

1)You need to ue the <total> qualifyer to return the BV for all values of Dim, for every row in custom.

Then add the set analysis to restrict it to "Begin"

=sum({<Dim={"Begin"}>} total BV) 

2) Use the top() function to return a value that as evaluated on the first row of your chart

=top(sum(BV) )

or

=top([BV]) //(referencing the column itself)

Is this what you are after?

Regards,

Erica

[#4]

It took me way to long attempting this when it was so simple.  thank you so much.  I was able to take that concept and extrapolate to my needs.  In the interest of cleaning up expressions to make them more readable, are you able to reference other fields in the same chart with this same process?  I tried doing sum({<BYA_BUCKET={'End'}>}TOTAL [Book Yield]) where Book Yield is the name of the column which contains a complicated underlying formula.  The reason I ask is because the ending expression looks like this and I have some further complications I need to add to the chart.  The further complications will be doing a different calculation if at a specific dimension.

//((groups' BY / End BV * groups' BV) - (groups' BV / End BV * Beg BY)) * 100

( (

  (sum(BOOK_YIELD*BASE_BOOK_VALUE)/sum(BASE_BOOK_VALUE))

  /

  sum({<BYA_BUCKET={'End'}>}TOTAL BASE_BOOK_VALUE)

  *

  sum(BASE_BOOK_VALUE)

  )

  -

  (

  sum(BASE_BOOK_VALUE)

  /

  sum({<BYA_BUCKET={'End'}>}TOTAL BASE_BOOK_VALUE)

  *

  (sum({<BYA_BUCKET={'Begin'}>}TOTAL BOOK_YIELD*BASE_BOOK_VALUE)/sum({<BYA_BUCKET={'Begin'}>}TOTAL BASE_BOOK_VALUE))

  )

) * 100