topic Re: dates and flooring in QlikView

dates and flooring

Fri, 25 Sep 2015 15:17:24 GMT

Hi community,

I am trying to add up all of the OPEN_QN_FLGs for each year only if the create date has been greater than 60 years.

I set the following variable:

LET v60Days = floor(today(1)) - 60;

My expression is the following:

sum({<YEAR_NO={$(vCurrentYear), CreateDate = {$(v60Days)}>} OPEN_QN_FLG), 0)

I am getting the error "No Data to Display" on the chart. I think it is because the CreateDate field is in this number format: xxxxx.xxxxxxx, so is different than the date format. Do I need to floor the create date to make it the same format as the variable? Can someone explain to me how to do so?

Thanks!

Re: dates and flooring

hic — Fri, 25 Sep 2015 16:23:15 GMT

The Today() function is already "floored" so you don't need to use the function when you assign the variable. But if the CreateDate has a fractional part, you need to use it there.

So, you do need to do the following:

Make sure that CreateDate and v60Days use the same format.
Make sure that CreateDate and v60Days both are integers (use Floor()).
Use single quotes around the date.

Hence the following will work if CreateDate is an integer not formatted as a date:

LET v60Days = Num(today(1) - 60);

sum({<YEAR_NO={$(vCurrentYear), CreateDate = {$(v60Days)}>} OPEN_QN_FLG), 0)

And the following will work if CreateDate is an integer formatted as a date:

LET v60Days = Date(today(1) - 60);

sum({<YEAR_NO={$(vCurrentYear), CreateDate = {'$(v60Days)'}>} OPEN_QN_FLG), 0)

HIC

Re: dates and flooring

Fri, 25 Sep 2015 18:44:30 GMT

Neither of those are working for me. The Create Date is a number format with many decimals.

Re: dates and flooring

Fri, 25 Sep 2015 19:44:01 GMT

what happens if you wrap the createdate filed within the date function

date(CreateDate)

this should convert the CreateDate field into a date format

Re: dates and flooring

Fri, 25 Sep 2015 19:47:47 GMT

I do not want to change it in the script permanently because for some of my other charts I need it to not be in date format. Is there somewhere I can do this to only change this chart? Can I do it in the Expressions box instead of editing the script?

Re: dates and flooring

Fri, 25 Sep 2015 19:50:30 GMT

yes - you should be able to wrap the create date field in the date funtion within the expression of the chart

You can also look at the number tab on the chart and set that field to be a date as well

Re: dates and flooring

Fri, 25 Sep 2015 20:44:47 GMT

Channing

any luck??

have a good weekend

Re: dates and flooring

hic — Fri, 25 Sep 2015 21:14:31 GMT

If the CreateDate has decimals, then you need to use floor(). It needs to be an integer. See Why don’t my dates work?

HIC

Re: dates and flooring

Fri, 25 Sep 2015 21:33:45 GMT

I think I just don't know the syntax. I am brand new to Qlikview so am still working on it. Thanks for your help!

Re: dates and flooring

Fri, 25 Sep 2015 21:43:59 GMT

LET v60Days = Num(today(1) - 60);

sum({<YEAR_NO={$(vCurrentYear), floor(CreateDate) = {$(v60Days)}>} OPEN_QN_FLG), 0)

Like that? That won't work it still says No data to display.

Re: dates and flooring

hic — Fri, 25 Sep 2015 21:49:18 GMT

No you can't use any functions to the left of the equals sign in Set Analysis.

The best solution is to do it in the script:

Date(Floor(CreateDate)) as CreateDate

If you can't touch the script, you must use relational operators in the Set Analysis, e.g.

sum({<YEAR_NO={$(vCurrentYear), CreateDate = {">$(v60Days)"}>} OPEN_QN_FLG), 0)

sum({<YEAR_NO={$(vCurrentYear), CreateDate = {">$(v60Days)<$(=v60Days+1)"}>} OPEN_QN_FLG), 0)

HIC