Using a variable value to pull another field value

[#1]

When I select an date in my calendar I store the value into a variable.  I want to use that variable value to pull a field value from my mastercalendar table which will give me the corresponding last year date.  Example:

mastercalendar:  'Date' = 7/12/2014 ,  'LY Date' = 7/12/2013.  In my app, I have a calendar object.  I select 7/12/2014 and that value is stored in my variable.  I want to use the variable to look in the mastercalendar table to get the LY Date.

[Anonymous]

in the expression u can use this to get the LYYear

Only(<Date={'=$(vSelectedDate)'}>LY Date)

[jpapador]

In a list box or variable you could do:

=Addmonths(Date, -12)

This will give you the date from last year based on your selected date.

[wms_manis]

Here is a way to make it even more dynamic.

I build an island that looks like this:

%Unit	CurrentUnit	LastUnit	PriorUnit
Year	CY	LY	PY
Quarter	CQ	LQ	PQ
Month	CM	LM	PM
Week	CW	LW	PW
Day	CD	LD	PD

Make a list box with %Unit and make sure always one is selected.

Now I can have an expression that compares one period to the next while allowing the user to pick the period to compare:

Use two variables:

vCurPeriodSet is defined as the following:

=only(CurrentUnit)

This returns CY if year is selected or CW if Week is selected.

vLastPeriodSet is defined as the following:

=only(LastUnit)

An example of how it could be used in an expression:

sum({$< $(vCurPeriodSet)={1}>}SumValue) This gives a value for the current period selected.

Sum({$<$(vLastPeriodSet)={1}>}somevalue) returns the value from the prior period.

For the example provided, I used flags in my calendar that were built using the inYear(),inMonth() functions.  The calendar will obviously need the all the fields listed in the island you create.