Expressions within Set Analysis

[quilldew]

Hi,

I have an issue where I am trying to count NULL's within the EndDate field. I've tried various ways to count them eg, Len( Trim( Field ) ) = 0, and have decided the easiest for this application was just to create a NullFlag .

I am basically trying to include an 'OR' operator between two different fields, and then include other criteria I need to be met within the count.

The following is an example of what works:

count({<StartDate={"<=01/07/2016"}

,EndDate={">=01/07/2017"}

,Diag1={"10*","11*"}

,OtherStartDate={"<=01/07/2016"}

>}[KEY Client])

+

count({<StartDate={"<=01/07/2016"}

,Diag1={"10*","11*}

,OtherStartDate={"<=01/07/2016"}

,NullFlag={'1'}

>}[KEY Client])

Is there a way to condense the above script to something like (EndDate={">=01/07/2017"} + NullFlag={'1'})? as it becomes very long when Diag? = n

I've tried the above and it doesn't work

Also if you have any suggestions as to how I could use Len( Trim( Field ) ) = 0 instead of creating the flag (so I can use it on apps I don't have script access for) that would be great.

The problem I have is that I can't get Len( Trim( Field ) ) = 0 to evaluate properly when EndDate has been created by using 'date(floor())' on a timestamp field.

[sunny_talwar]

For the second expression do you get 1 for any KEY Client before July 1st? If not, then may be all you need is a DISTINCT in there

Count(DISTINCT {<StartDate={"<=01/07/2016"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}, [KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

[quilldew]

Still lots and lots of EndDate's before 1st July. For some reason when I change the Date in makedate() it only changes the total by 1. There should be hundreds extra.

Right got to take a break for the weekend. Back on monday and thanks for the help Sunny

[sunny_talwar]

Sounds good

[pradosh_thakur]

can you try something like this without using the null flag

i might be wrong but the logic is enddate should consider all values with null then exclude which are less then the required data.Please adjust the date as required.

count({<StartDate={"<=01/07/2016"}

,Diag1={"10*","11*}

,OtherStartDate={"<=01/07/2016"}

,EndDate=-{"<01/07/2017"}

>}[KEY Client])

regards

Pradosh