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!
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