Set Analysis or Join

[#1]

Table1 has three fields, a Ticker, Price, and Reference

Table2 has two fields, a Ticker and PickReference

I would like to display in a Straight Table the Ticker, PickReference number, and the corresponding Price

This statement works if only one Ticker is selected:    =Sum({<Reference={$(=[PickReference])} >}Price)

Is there a way to change this statement to show the entire table, all tickers, all referenced prices?

Or, would a Join during the load be a better solution?

Attached is my project.

Thanks!

[Gysbert_Wassenaar]

If PickReference and Reference contain the same data then rename PickReference to Reference. You could then also join the two tables so you end up with only one. It's not necessary, but an option.

The set expression calculates one set per chart, not a set per row. So it will very likely not do what you expect.

[Anonymous]

Hi Chris,

Try the expression.:

FieldValue('Price',  FieldIndex('Reference' ,PickReference) )

Please, see attached.

Regards

[#4]

If it has to be a sum, you can try something like:

=Sum({<Reference=PickReference >}Price)

Problem is you lose the chart if you clear PickReference selections..

[ronaldocarrijo · Accepted solution]

Use the formula:

Sum(if(Reference=PickReference,Price))

[#6]

Close!  But the DORM value is returning the CBST value for that date.  Looks like they are all returning the CBST values.

[#7]

CBST is returning a value of 126, I don't know where that is coming from.

My formula, close to what you suggested, works when only one Ticker is selected:

=Sum({<Reference={$(=[PickReference])} >}Price)

[#8]

Wow!   Simplicity!  

Thanks!