Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Date value in a variable

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.

8 Replies
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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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
Not applicable
Author

Hello!!!

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

Anonymous
Not applicable
Author

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
Not applicable
Author

arsalanam,

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

You select dates the differents years.

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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

Data.jpg