Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
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
Neither of those are working for me. The Create Date is a number format with many decimals.
what happens if you wrap the createdate filed within the date function
date(CreateDate)
this should convert the CreateDate field into a date format
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?
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
Channing
any luck??
have a good weekend
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
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!
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.