topic Re: if statement and date functions in QlikView

if statement and date functions

Fri, 25 Sep 2015 14:13:27 GMT

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

jonathandienst — Fri, 25 Sep 2015 14:17:35 GMT

Try this:

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

Re: if statement and date functions

cjohnson — Fri, 25 Sep 2015 14:25:34 GMT

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

Re: if statement and date functions

Fri, 25 Sep 2015 14:49:40 GMT

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

maxgro — Fri, 25 Sep 2015 17:50:31 GMT

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

Fri, 25 Sep 2015 18:47:46 GMT

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

cjohnson — Fri, 25 Sep 2015 19:02:52 GMT

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

maxgro — Fri, 25 Sep 2015 19:08:53 GMT

try

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

Re: if statement and date functions

Fri, 25 Sep 2015 19:16:34 GMT

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

maxgro — Fri, 25 Sep 2015 19:24:46 GMT

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