How to use the value of one selection as the value for another field?

[#1]

Team,

I am trying to incorporate Shipments (invoiced orders) and Incoming Orders in one app.  Each sales record has both an invoice date (shipment) and a created date (incoming).  In the app, I would like the user to be able to select Ship Yr and Ship months, without also having Created Yr and Created Mo in the app; and have the app show calculations for both Shipments, and also Incoming orders for the same selections, using the Ship Yr and Mo as the Created Yr and Mo.  Is there a way to have an expression use a selection from one field as the filter for another field?  I was trying some formulas, but it looks like it is taking only the intersection of those orders where the Ship Yr = the Created Yr (that is, where orders were entered and shipped in the same year).  I want to show all orders in the Created Yr, whether they shipped in that year or not.  This formula appears to be resulting in the intersection:

({$<[Incoming Yr]={$(=[Ship Yr])}>}[Net Revenue])

Does anyone have any ideas?

Thanks for the help!

[#2]

Please post sample data or qvw.

[jerem1234]

Try using the P() function in your set analysis:

({$<[Incoming Yr]=P([Ship Yr])>}[Net Revenue])

Hope this helps!

[#4]

Oh sorry, rookie mistake, here's an example.

Created Mo	Created Yr	Net Revenue	Ship Mo	Ship Yr
1	2013	$100	5	2013
11	2012	$200	3	2013
8	2013	$250	-	-
Select in QV Ship Yr =	2013
Should result in:
Shipments	$300
Incoming	$350

$300 Shipped in 2013, while $350 came in as orders in 2013.

[#5]

Thanks for the reply.  I tried this and appears to be the same result.

[jerem1234]

Have you tried this?

sum({1<[Created Yr]=P([Ship Yr])>}[Net Revenue])

and

sum({1<[Ship Yr]=P([Ship Yr])>}[Net Revenue])

[Josh_Good]

If I understand you correctly, you want the year selection to apply to the Create Yr only for the incoming calculation and the year selection apply to the Ship Yr only for the Shipments calculation.

To do this, I would recommend creating a date island and then using the selection in the data island in set analysis to drive the results.  Attached is an example.  The set expression get a little complex because you need to return all the selection in correct format for set analysis.

"Chr(39)" will return single quote so inside the dollar sign expansion.  I added a text box as an example of what is being returned by one of the dollar sign expansion (easier to show than explain).

[#8]

Holy cow!  It works, but I think I have to look at this multiple times to digest it because it looks like a foreign language to me.  Impressive.  There's probably no easier way, huh?  THANKS.

[Josh_Good]

You could make the express feel a whole lot nicer by using variables but in the end the reason it looks so bad is single quote is an escape character so you need to use Chr(39)