topic Re: PB DATE in QlikView

PB DATE

Thu, 10 Jul 2014 10:00:19 GMT

Hello,

I am trying to calculate an indicator whose objective is to calculate the Number of Findings still open 6 months after date of notification. So basically Each Findings, I have a closing date (CLOSE_DATE) and a notification date and I want to calculate the number of Findings that have a closing date than 6 months of their date of notification.

That's what I tried to do but it does not work :

Count ({$<CLOSE_DATE={">=$(=date(AddMonths(date_notification,6))"}>} DISTINCT FINDINGS_ID)

Thank you in advance.

Re: PB DATE

Gysbert_Wassenaar — Thu, 10 Jul 2014 10:24:44 GMT

Make sure that the fields CLOSE_DATE and date_notification actually contains data values and not string values. Also each finding should have only one closing date and only one notification date. Otherwise the comparison of the fields won't work.

Re: PB DATE

ali_hijazi — Thu, 10 Jul 2014 10:27:35 GMT

make sure in the load statement to have both Close_date and date_notification as numbers

load num(daystart(Close_date)) as Close_Date...

Re: PB DATE

Thu, 10 Jul 2014 11:12:50 GMT

Hello,

I loaded the two dates in Num format but it does not work yet.

num(daystart(date_notification)) as date_notification,

num (daystart ( CLOSE_DATE)) as CLOSE_DATE,

Thank you in advance.

Re: PB DATE

ali_hijazi — Thu, 10 Jul 2014 11:32:01 GMT

us then the following syntax:

Count (if(Close_Date >=num(DayStart(AddMonths(date(date_notification),6)))), DISTINCT FINDINGS_ID))

it should work

Re: PB DATE

Thu, 10 Jul 2014 11:45:44 GMT

Thank you very much.

It gives me that the expression is not correct.

Re: PB DATE

ali_hijazi — Thu, 10 Jul 2014 11:47:29 GMT

I added an additional parenthesis by mistake; the following should work

Count(

if(

Close_Date >=num(DayStart(AddMonths(date(date_notification),6))), DISTINCT FINDINGS_ID))

Re: PB DATE

Thu, 10 Jul 2014 11:57:42 GMT

it does not accept the DISTINCT in the expression !!!!!

Re: PB DATE

ali_hijazi — Thu, 10 Jul 2014 11:58:35 GMT

Count(DISTINCT

if(

Close_Date >=num(DayStart(AddMonths(date(date_notification),6))), FINDINGS_ID))

Re: PB DATE

DavidFoster1 — Thu, 10 Jul 2014 12:05:11 GMT

I would suggest a simple solution would be to a flag field into your findings data that contains a 1 for findings within 6 months and a 0 for the rest.

That way your expression would then be SUM(Within6MonthsFlag)

Re: PB DATE

Thu, 10 Jul 2014 12:11:27 GMT

it works. Now I just need to verify that my data are correct.

I come back to you if i have another problem.

Thank you very much.

Re: PB DATE

ali_hijazi — Thu, 10 Jul 2014 12:22:40 GMT

you're welcome at anytime do not hesitate to ask me

Re: PB DATE

Thu, 10 Jul 2014 12:32:22 GMT

Thank you very much.

I will try this method too, so I have two methods.

Re: PB DATE

Thu, 10 Jul 2014 13:22:27 GMT

Hello,

When I add the set analysis, it does not work.

That's my expression :

Count({$<AUDIT_STATUS_ID<>{'3'} >} Distinct if(Close_date >=num(DayStart(AddMonths(date(date_notification),6))), FINDINGS_ID))

Thank you.

Re: PB DATE

Thu, 10 Jul 2014 13:26:30 GMT

Sorry for the inconvenience. It works.

Re: PB DATE

ali_hijazi — Thu, 10 Jul 2014 13:58:03 GMT

use the following in your set analysis

AUDIT_STATUS_ID ={"*"}-{3}