topic problem with a revised expression in QlikView

problem with a revised expression

josephinetedesc — Tue, 28 Jun 2016 02:12:49 GMT

count(if(aggr(only(dateQA - dateCT), Pat_ID1) <= (LengthDays*7/5),1))

Hi all

the above expression works but ...

I want to improve it!

a. using NetWorkDays(dateQA,dateCT) instead of (dateQA - dateCT) This could be giving me a problem because it is giving a "fraction" of a date?? ...

LengthDays*7/5 was an attempt to allow for weekends but with NetWorkDays - the 7/5 compromise will not be needed.

but I then I start getting errors ...

So what I am trying to say is:

a particular Pat_ID1 has x number of days between dateQA and date CT

if this x number of days is less than or equal to a specified LengthDays then give them a count of 1.

Re: problem with a revised expression

settu_periasamy — Tue, 28 Jun 2016 02:43:34 GMT

Have you tried with 'floor' function?

Networkdays(floor(dateQA),floor(dateCT))

Re: problem with a revised expression

josephinetedesc — Wed, 29 Jun 2016 13:06:34 GMT

I found out what I have been doing wrong!

The difference in the first and second image is that the second image had as an expression '1' - this was because I wanted all rows to show.

However I could solve the problem (without the expression "1") - by ensuring that the "Suppress zero-values" was unchecked! I now need to check how negative values are shown.

Re: problem with a revised expression

josephinetedesc — Wed, 29 Jun 2016 13:18:03 GMT

If the difference between 2 dates is negative -the function NetWorkDays() gives 0 .. How am I able to keep the 0 value in rowNum3, but not the 0 in rowNum6?

Re: problem with a revised expression

settu_periasamy — Wed, 29 Jun 2016 17:07:29 GMT

Hi,

Have you got resolved?, if not, try like this..

=if(dateCT<dateQA,

NetWorkDays(dateCT,dateQA),

NetWorkDays(dateQA,dateCT))