Date value in a variable

[Anonymous]

Trying to assign a vlaue to a variable that should be the maximum available date of previous year based on user selected date:

=Date(max({<P_Year={"$(=year(P_Date)-1)"}>} P_Date),'DDMMMYY')

First, I get previous year and then get the maximum date of that year. Due to some reason, I do not get any reult.

[Gysbert_Wassenaar]

Make sure P_Date contains real date values, i.e. numbers, not string values. Check that =num(P_Date) returns a number if you select one P_Date. If P_Date contains strings you'll have to use the date# function to create a date from that string first. It's best if you do that conversion in the load script.

[Anonymous]

P_Date contains real date values. I changed it to:

=Date(Max({1<P_Year={"$(=Year(AddYears(P_Date,-1)))"}>} P_Date),'DDMMMYY')

It works only if I have '1' in set expression but now I only get one max date for all regions. I wanted separate max date for each.

[Anonymous]

Hello!!!

Can you add a column year of date? Maybe It can help you.

[Anonymous]

Andrea, I was able to get to this point. But the issue is I get max date for every situation as one date e.g. July 25th in your example, I need it flexible, if user selects any date from the list box, this should change accordingly.

[Anonymous]

arsalanam,

I add new values to that you see the chage according the date selected.

You select dates the differents years.

[Anonymous]

thank you for your help Andrea!
But i'm trying to get max date of previous year for any selected date, your attached file shows max date of same year.

[Anonymous]

I understand, the table was just to check some data, the formula is in the text box. Eg

to select the date 01/05/2011 in the text box shows 12Apr10, it´s the max date of the previous year(2010)

to select the date 12/07/2012 in the text box shows 17May11, it´s the max date of the previous year(2011).

to select the date 22/07/2013 in the text box shows 25Jul12, it´s the max date of the previous year(2012).

to select the date 25/03/2010 in the text box shows null '-', because not exists information for previous year(2009)

Please, You verify.

[Anonymous]

Thank you so much, it helped a lot.

I'm facing a strange situation. I get a date for last region (middke column) which is different from rest but when i put it in the expression, I do not get any value. Screen shot attached.

Middle column gives correct values (as you suggested):
=Date(max({1<C_Year={$(=Max(C_Year)-1)}>} C_Date),'DDMMMYY')

Last column which works fine for rest but not for last row:

=(Num_Field+1)/(only({<C_Date={"$(=Date(max({1<C_Year={$(=Max(C_Year)-1)}>} C_Date),'DDMMMYY'))"}>}Num_Field)+1)-1