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.

[quilldew]

Only one EndDate per [KEY Client], and I'm not looking for either the min() or max() in any of the script.

Could it be down to the fact that EndDate is created from date(floor(EndDateTime)) ?

[sunny_talwar]

Check this

Count({<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])

Was not sure if 01/07/2017 was Jan 7th or July 1st... I am assuming it was Jan 7th, but change the values in Make date to MakeDate(2017, 7, 1) if it was July 1st

[quilldew]

It's 1st of July I'm looking at.

So I tried MakeDate(2017,7,1) and it is unfortunately still showing me lots of dates before that date.

I've just created a couple of listboxes, one for StartDate and one for EndDate.

I've noticed that all the entries for EndDate are right aligned, whereas for StartDate they are left aligned. Shouldn't they be the same? When I display them as timestamp they both have the time as 00:00 so the floor() has worked in the script at least.

Also every time I've used 'Len( Trim( Field ) ) = 0' on StartDate it gives me the expected results, but not on EndDate.

[sunny_talwar]

Can you create a straight table with the following and share an image?

Dimension

[KEY Client]

Expressions

=Only({1} EndDate)

=Only({1} EndDate) >= MakeDate(2017, 7, 1)

=Len(Trim(Only({1} EndDate))) = 0

[quilldew]

I can't post an image unfortunately as I've had to change the actual fieldnames we use (work are funny about making them public.

But I can tell you that for:

1) The EndDate was shown for each [KEY Client]

2) Shows -1 for any EndDates >= 1st July, and 0 for everything else including when EndDate is empty

3) Shows 0 for EndDate if it exists and -1 for empty.

So it looks like it's working properly. Just not within the set analysis.

[sunny_talwar]

This is what I would have expected... which is good.... My guess is that this is working

Count([KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

But something else isn't working

Try adding these two expressions in the above chart that you created

1) Count([KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

2) Count({<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])

and tell me what you see.... (remember that the dimension is still KEY Client)

[quilldew]

I swapped the makedate part to (2017,7,1) as i'm looking for July. I did try it both ways though.

Okay, so it looks like there is more than 1 EndDate per [KEY Client] as 2) is giving counts of 3 and 2 and 1, but they seem to be completely random apart from the fact they ONLY occur when the EndDate is empty. But there are plenty of records with empty EndDate's that have 0, and EVERY record with an EndDate is 0.

For 1) everything is empty (as in -). Which i'm assuming it shouldn't be.

[quilldew]

Actually 1) has an error in expression

[sunny_talwar]

My bad, missing {<

Count({<[KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

[quilldew]

Yeah I spotted it just after I posted lol.

Now 1) is showing 1 for all EndDate's >= 1st July or empty. Which seems correct.