9 Replies Latest reply: Sep 25, 2015 3:24 PM by Massimo Grossi

if statement and date functions

Hi Community,

I am trying to make a chart like the one below.  I have many fields, two of which are: Open QNs (a columns of 0s and 1s, 0 meaning the QN is not open and 1 meaning that the QN is open, so we care about the 1s) and Createdate (when the QN was created, in number format).  I assigned a variable for each year.  For 2015 for example, this is my expression:

if(today() - CreateDate >60,

sum(

{<YEAR_NO={\$(vCurrentYear)}>}

OPEN_QN_FLG), 0)

Basically for each year, I want a sum of all of the open flags (1s) that have been opened for more than 60 days.

Can anyone help me out?

Thanks!

• Re: if statement and date functions

Try this:

sum({<YEAR_NO={\$(vCurrentYear), CreateDate = {">\$(=Date(Today() - 60))"}>} OPEN_QN_FLG), 0)

• Re: if statement and date functions

Thanks!  Now, the problem is that the create date is a 5 digit number with many decimals, and the date is not.  So i made the following variable:

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

So my new equation is:

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

I am still getting "No Data to Display" on the chart.  I think it is because I need to floor the create date?  Do you know how to do that?

• Re: if statement and date functions

if you don't need the time part (decimal) of the CreateDate you can remove in the script using

Floor(CreatedDate) as CreatedDate

or Date(Floor(CreatedDate)) as CreatedDate

also check the CreatedDate and the bold have the same format (both number or both date)

sum({<YEAR_NO={\$(vCurrentYear), CreateDate = {">\$(=Date(Today() - 60))"}>} OPEN_QN_FLG), 0)

• Re: if statement and date functions

I need the CreatedDate to be in the current format with the decimals for some other stuff I am doing, so do not want to change it in the overall script because it would affect other stuff. So just for this instance, how do I make that field the same format as the date (LET v60Days = floor(today(1)) - 60;)

• Re: if statement and date functions

Hi Channing,

Instead of using the today() function why don't you try using the now() function? That will give you a timestamp instead of date so in number format (num(now()) would include decimals (like your createdate field).

If you use the function now(0) it will be evaluated based on the script execution..

Camile

• Re: if statement and date functions

try

sum({<YEAR_NO={\$(vCurrentYear), CreateDate={">\$(=(Today() - 60))"}>} OPEN_QN_FLG), 0)

• Re: if statement and date functions

That doesn't work .. probably because create date is a number and TODAY() - 60 probably returns date format i'm guessing??

• Re: if statement and date functions

today()-60 should be a number, try in a textbox or char; you can add a num but I think it doesn't change

• Re: if statement and date functions

Based on the chart you show above I'm not sure why you need to have year as part of your set analysis expression (instead of as a dimension). Can you instead do the following:

Dimension: YEAR_NO

Expression: sum({<CreateDate = {"<\$(=Date(Today() - 60))"}>} OPEN_QN_FLG)

Thanks,

Camile