Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

vkish16161
Contributor III

P and E Syntax issue??

All,

Is there anything wrong with below image? why is P not highlighting in Blue?

The intent here is to only get count of claims where Opened date is the same as Reported date.

P and E.JPG

My other solution that works is Count (if([Claims Opened Date]=[Claims Reported Date],[Claims Number],null())) but not sure why P and E wouldn't work?

hicrwunderlichmarcus_sommermtostalwar1

Tags (5)
1 Solution

Accepted Solutions
Employee
Employee

Re: P and E Syntax issue??

The expression

   <[Claims Opened Date]=P([Claims Reported Date])>

will not do what you want: P() is a set, i.e. it contains multiple values. So the expression will be equivalent to making a selection of multiple values in [Claims Opened Date]. You instead want a comparison made value-by-value, where the two dates are compared.

Generally, set analysis cannot be used for a row-by-row comparison this way.

Further, the search

   count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

will not always work either: Here, a hypercube with [Class Number] as dimension will be created in the background, and

   [Claims Opened Date] = [Claims Reported Date]

will be used as measure. Records with this evaluated as TRUE will be included in the search result. But classes with multiple open dates and multiple report dates will be evaluated as

   NULL = NULL

which in turn is FALSE, even if there are individual dates that match.  So, the search will not (always) work either.

The solution with the If() function will work fine, though.

HIC

7 Replies
ali_hijazi
Honored Contributor

Re: P and E Syntax issue??

try:

count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

your expression in the picture should work unless the 2 dates fields have different format

try converting both to numbers

vkish16161
Contributor III

Re: P and E Syntax issue??

Ali,

The below will work, I tried this already :

count({<[Claims Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>}[Claims Number])


BUT


is there a reason why "P" isn't highlighting in Blue?


and also, I believe, set analysis takes the numeric part of the field, so conversion shouldn't matter.


ali_hijazi
Honored Contributor

Re: P and E Syntax issue??

no idea why it is not being highlighted in Blue but it should work
last week I was working on expression to get the customers not served in a selected period of time and I used the E() and it works perfectly despite the fact that the E is not highlighted in blue

date(

max(

{1

<

salesman_name=$::salesman_name,

company_name=$::company_name,

branch_customer.customer_code = E(

{

<

trx_type={'Sales'}

,branch_customer.customer_status={'active'}

,branch_customer.customer_code = {"=Sum({<trx_type={'Sales'}>} amount_usd)>0"}

>

} branch_customer.customer_code

)

,trx_type={'sales'}

,branch_customer.customer_status={'active'}

,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=

,trx_date={'<=$(vEOM_LastMonth)'}

>

}trx_date)

)

Employee
Employee

Re: P and E Syntax issue??

The expression

   <[Claims Opened Date]=P([Claims Reported Date])>

will not do what you want: P() is a set, i.e. it contains multiple values. So the expression will be equivalent to making a selection of multiple values in [Claims Opened Date]. You instead want a comparison made value-by-value, where the two dates are compared.

Generally, set analysis cannot be used for a row-by-row comparison this way.

Further, the search

   count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

will not always work either: Here, a hypercube with [Class Number] as dimension will be created in the background, and

   [Claims Opened Date] = [Claims Reported Date]

will be used as measure. Records with this evaluated as TRUE will be included in the search result. But classes with multiple open dates and multiple report dates will be evaluated as

   NULL = NULL

which in turn is FALSE, even if there are individual dates that match.  So, the search will not (always) work either.

The solution with the If() function will work fine, though.

HIC

vkish16161
Contributor III

Re: P and E Syntax issue??

rwunderlich

According to Henric below may not work.

1.JPG

Highlighted
Employee
Employee

Re: P and E Syntax issue??

The construction

   {<RecID={"=F1=F2"}>}

will work excellently if RecID has a grain that is fine enough, i.e. if there are no multiple values in F1 and F2 for any RecID. The name "RecID" implies that this is an ID per record - an ID on the lowest level. And then the expression will work.


In your case, you have

   {<Class={"=Date1=Date2"}>}

which is an expression with identical structure, and the same is of course true here: If Class has a grain fine enough, then it will work. But it will not work if a Class has multiple values of Date1 (or Date2) associated with it. And the name "Class Number" sounds as if this is a possibility.


HIC

vkish16161
Contributor III

Re: P and E Syntax issue??

Thanks Henric for clarifying.

Also, Set Expression Builder was released in Qlik Cloud today but I believe there's a minor flaw in it.

Could you please clarify it?

Here's the link: Bug ?? in Set Expressions Builder in QS Nov 2018?

Thanks!

hic

Community Browser