topic Re: Date value in a variable in QlikView

Date value in a variable

Anonymous — Fri, 26 Jul 2013 15:55:31 GMT

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.

Re: Date value in a variable

Gysbert_Wassenaar — Fri, 26 Jul 2013 16:07:41 GMT

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.

Re: Date value in a variable

Anonymous — Fri, 26 Jul 2013 16:29:11 GMT

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.

Re: Date value in a variable

Anonymous — Fri, 26 Jul 2013 17:34:37 GMT

Hello!!!

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

Re: Date value in a variable

Anonymous — Fri, 26 Jul 2013 18:02:49 GMT

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.

Re: Date value in a variable

Anonymous — Fri, 26 Jul 2013 20:04:53 GMT

arsalanam,

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

You select dates the differents years.

Re: Date value in a variable

Anonymous — Fri, 26 Jul 2013 21:07:51 GMT

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.

Re: Date value in a variable

Anonymous — Fri, 26 Jul 2013 23:34:07 GMT

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.

Re: Date value in a variable

Anonymous — Mon, 29 Jul 2013 19:31:50 GMT

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