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.

[Anil_Babu_Samineni]

May be this?

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

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

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

,OtherStartDate={"<=01/07/2016"}> + <NullFlag={'1'}>

>}[KEY Client])

+

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

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

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

,NullFlag={'1'}

>}[KEY Client])

[quilldew]

Give me an error in expression unfortunately. It was one of the first things I thought of

[quilldew]

Actually I put it as

EndDate={">=01/07/2017"}> + <NullFlag={'1'}>

But neither work.

[sunny_talwar]

May be this

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

[sunny_talwar]

Or this

Count(

{<StartDate={"<=01/07/2016"}, EndDate={">=01/07/2017"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}>

+<StartDate={"<=01/07/2016"}, Diag1={"10*","11*}, OtherStartDate={"<=01/07/2016"}, NullFlag={'1'}>}

[KEY Client])

[Anil_Babu_Samineni]

I believe 1st one should work, You are Rock JI

[quilldew]

This one works Sunny, and is a slight improvement on mine, but is still of a comparable length

[quilldew]

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

This is the kind of thing I am looking for, significantly shorter than my code, but it unfortunately doesn't work for me.

It is returning lots of records where the EndDate is before 01/07/2017.

[sunny_talwar]

Do you have more than one EndDate for KEY Client field? If you do, are you looking for Min() or Max() End Date?