topic Re: set analysis dates <= not working in QlikView

set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 14:08:36 GMT

I'm having difficulty making the <= vCurrentYearDate work in my set analysis. The variable, vCurrentYearDate gives me the last months prior month end if a month year selection is not made. The variable is working and gives me the correct date, which is the last day of the month, however the set analysis is not giving me the data for the last day of the month. It is treating it like a < not a <=. I have removed all time stamps from the data. If I change the variable to be the first day of the next month, I can get the last day of the month, but I would like to figure out why the <= is not working properly with this set analysis.

Thanks so much!

Jen

sum({<Year =, Month = ,Transaction_Date={">=$(=(vCurrentYearStart))<=$(=(vCurrentYearDate))"}>}Invoice_Amount)

vCurrentYearDate = date(if(getselectedcount(Year)<>1 and getselectedcount(Month)<>1, date(Monthend(AddMonths(max(Transaction_Date),-1))), date(Monthend((max(Transaction_Date))))))

Re: set analysis dates <= not working

giakoum — Thu, 26 Feb 2015 14:11:40 GMT

do you have a sample app for that?

Re: set analysis dates <= not working

Thu, 26 Feb 2015 14:16:56 GMT

Try using single quotes and wrapping the variable in the Date() function. And don't forget a space between the >= / <= groups:

Sum({<Year =, Month = ,Transaction_Date={'>=$(=Date($(vCurrentYearStart))) <=$(=Date($(vCurrentYearDate)))'}>}Invoice_Amount)

Re: set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 14:36:01 GMT

Thanks Dirk!

When I added the $ after the Date($( it returned 0, so this is what it looks like now and I'm still getting the same results where it is not picking up the last day of the month.

Transaction_Date={'>=$(=Date((vCurrentYearStart))) <=$(=Date((vCurrentYearDate)))'}

Thanks for you help. I really appreciate it.

Jen

Re: set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 14:45:07 GMT

Hi Jen,

Try:

=sum({<Year =, Month = ,Transaction_Date={">=$(vCurrentYearStart)<=$(vCurrentYearDate)"}>}Invoice_Amount)

Regards

Neetha

Re: set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 14:50:06 GMT

Thank you Neetha!

Just tried and it is still not picking up the last day of the month.

Re: set analysis dates <= not working

menta — Thu, 26 Feb 2015 14:52:47 GMT

put your formula into a chart with no name in expression and verify which part doesn't work

Re: set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 15:03:31 GMT

It's the <= that does not work. It is ignoring the last day of the month, so the <= is behaving like a < . If I change the variable vCurrentYearDate to be the 2/1/15, and use the <= it will pick up 1/31/15 data and not 2/1/15 data. As it is right now vCurrentYearDate = 1/31/15 and the last date showing in the range is 1/30/15 not 1/31/15 as I would expect.

Re: set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 15:10:10 GMT

Hi Jen,

Try:

=sum({<Year =, Month = ,Transaction_Date={">=$(vCurrentYearStart)<=$(=vCurrentYearDate)"}>}Invoice_Amount)

Regards

Neetha

Re: set analysis dates <= not working

chrismarlow — Thu, 26 Feb 2015 15:10:22 GMT

I had an issue once where I had used MonthEnd to create a date & a join failed as it also included the timestamp (to give last millisecond of the actual day).

Might your transaction dates actually have a time on them & therefore be after date is it is calculating as 1/31/15 00:00:00?

Re: set analysis dates <= not working

menta — Thu, 26 Feb 2015 15:12:58 GMT

to solve the problem you can use floor

floor(date(Monthend(AddMonths(max(Transaction_Date),-1)))), Floor(date(Monthend((max(Transaction_Date)))))))

Re: set analysis dates <= not working

Anonymous — Thu, 26 Feb 2015 15:48:22 GMT

Thank you Christopher! Yes, it looks like the time stamp is the issue. I put Date(Transaction_Date) as Transaction_Date in my script, which I thought would remove the time from the date. Also, I SET TimestampFormat='M/D/YYYY' in the script as well. Is there a better way to remove the time from the date when loading the data?

Re: set analysis dates <= not working

chrismarlow — Thu, 26 Feb 2015 15:53:06 GMT

I think Carlo's floor would do it, but wrap it around your Transaction_Date (that you might need to cast back into being a date).