dates and flooring

[#1]

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!

[hic]

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)

or

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

HIC