P and E Syntax issue??

[vkish16161]

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.

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?

hic‌ rwunderlich‌ marcus_sommer‌ mto‌ stalwar1‌

[ali_hijazi]

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]

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]

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)

)

[hic · Accepted solution]

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]

rwunderlich‌

According to Henric below may not work.

[hic]

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]

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