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

dates and flooring

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!

10 Replies
hic
Former Employee
Former Employee

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

Not applicable
Author

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

Not applicable
Author

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

date(CreateDate)

this should convert the CreateDate field into a date format

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

Channing

any luck??

have a good weekend

hic
Former Employee
Former Employee

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

Not applicable
Author

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!

Not applicable
Author

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.