use of underscore in formula as a comparison operator?

[daveatkins]

trying to decipher some inherited code...is there any documentation on what this is for?

if( (today()-SomeDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],'Outlier','Not an Outlier')

It appears to be a > test, but would love to find explanation. It is part of a big if cascade

[Lech_Miszkiewicz]

Hi @daveatkins 

Hard to understand your enquiry without seeing whole picture.

• Where do you see this long expression?
• How does  the whole expression look like?
  • I understand it is a long "If" statement but maybe there is more to it (like Replace ()) or similar or maybe some $ sign expansion
• Are you seeing this in NPrinting or in Qlik Sense somewhere
  • If you see it in NPrinting then first thing I would do would be to check the Qlik Sense expression directly in Qlik Sense
• Providing the actual screenshots of that expression in Qlik Sense would help understand the details

tricky one 🙂

[daveatkins]

The expression is in an Excel Nprinting template. The entire expression is:

<=if(if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],'Outlier','Not an Outlier') = 'Outlier','Current Outlier'//High,if(rank(TOTAL if(if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],'Outlier','Not an Outlier') = 'Outlier',((today()-StartDateTime)-if(isnull(StartVariableG),0,StartVariableG))+1,class_1_probability))_=sum( TOTAL if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],1,0))+(.25*(count(TOTAL distinct(UserID))- sum( TOTAL if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],1,0)))),'High'//Medium,if(rank(TOTAL if(if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],'Outlier','Not an Outlier') = 'Outlier',((today()-StartDateTime)-if(isnull(StartVariableG),0,StartVariableG))+1,class_1_probability))_=sum( TOTAL if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],1,0))+(.5*(count(TOTAL distinct(UserID))- sum( TOTAL if((today()-StartDateTime) _ (([75thPercentile]- [25thPercentile])*1.5)+[75thPercentile],1,0)))),'Medium','Low')))>

The expression will not work as a copy/paste back into a qlik expression so this must involve some nprinting syntax issues. The main question we had looking at this is what is the purpose of the underscores between values being compared? My best guess is that _ is a sort of failover comparison operator in nprinting.

What this expression is doing is comparing an individual observation data point, [today() - startDateTime] to a table of population averages specific to this class of individual, StartVariableG and an associated percentile range. So I believe what this does is classify the data point as an outlier if it outside the confidence interval of what is represented by that population average.

We want to recode this into something comprehensible but need to understand why it currently works...there are other reports that have this kind of coding embedded in them as well...hope to hear back from the author by next week 🙂

The reason I shortened the expression is that I see comment // very early in the code, so I would think we could ignore all that other stuff.

[daveatkins]

eventually I found the source expression. the nprinting stuff is just garbage; the _ is a > sign in the QlikSense expression. I guess Nprinting does some things to avoid excel conflicts?

[Ruggero_Piccoli]

Hi,

It starts with a < and ends with a > so, from a Qlik NPrinting point of view, it is an entity tag. It will not be calculated by Qlik NPrinting, it will be replaced with the value that arrives from the connected Qlik Sense app. I suppose it is something like the tag of a column of a table. 

You can open the Qlik NPrinting template, in the Properties panel for this entity you will be able to see the name of the source connection. You open that connection in the Connection tab of the Web Console and you will be able to retrieve the Qlik Sense appID where you can investigate the formula. 

As general rule, Qlik NPrinting doesn't calculate the values of formulas. If you add a Qlik Sense formula it will be sent to Qlik Sense that returns the value and Qlik NPrinting places it in the report. Excel formulas are copied in the generated reports and will be calculated by Excel. The consequence is that you have to use Qlik Sense or Excel syntax.

Best Regards,

Ruggero

[daveatkins]

Thanks; this is eventually how I was able to track down the entity to a community sheet in an app. This is helpful to understand that these items are not formulas, but references, so it is better to think of it as an reference value/lookup, not try to parse the characters in nprinting.