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

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!

open QNs.png

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Try this:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
cjohnson
Partner - Creator II
Partner - Creator II

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

Not applicable
Author

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?

maxgro
MVP
MVP

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)

Not applicable
Author

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;)

cjohnson
Partner - Creator II
Partner - Creator II

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).

image34.png

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

Camile

maxgro
MVP
MVP

try

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

Not applicable
Author

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

maxgro
MVP
MVP

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