Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arsalanam
Creator
Creator

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
arsalanam
Creator
Creator
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.

andrea_reyes
Contributor III
Contributor III

Hello!!!

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

arsalanam
Creator
Creator
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.

andrea_reyes
Contributor III
Contributor III

arsalanam,

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

You select dates the differents years.

arsalanam
Creator
Creator
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.

andrea_reyes
Contributor III
Contributor III

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.




arsalanam
Creator
Creator
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