Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
quilldew
Creator
Creator

Expressions within Set Analysis

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.

23 Replies
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])

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
quilldew
Creator
Creator
Author

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

quilldew
Creator
Creator
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
quilldew
Creator
Creator
Author

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

quilldew
Creator
Creator
Author

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?