topic Re: Set Analysis - Handling complexities - Arithmetic, Fields, Variables, Variables within variables, Greater than etc in App Development

Set Analysis - Handling complexities - Arithmetic, Fields, Variables, Variables within variables, Greater than etc

Anonymous — Thu, 04 Jan 2018 18:51:49 GMT

I am somewhat new to QlikSense, but am getting a hang of it. Set Analysis is probably my weak spot and no matter how much I read, I tend to forget everything within hours. Plus, the guides don't do a great job explaining how to handle more complex/'tricky' situations (aka Level II or III complexity) than what they deem complex (aka Level 1 complexity) .

I went through this, this and this, still no dice. The only thing left for me to do is to bang my head to the wall and see if something shakes up.

The actual file is pretty big and proprietary, so can't post it here... so I would appreciate if you can give me an idea and point me in the right direction.

GOAL:

I have an expression that works, but I need it in the form of set analysis. Simple, right?

BACKGROUND:

//IN LOAD SCRIPT - set some default values

SET dMinSOS = 20000;

SET dMaxSUSPD = 225;

SET dSUR = 1;

SET dSOR = 0.3;

//IN LOAD SCRIPT - generate some custom inputs so user can select a value

FOR i = 1 to 20

LET counter = i*5000;

LOAD * INLINE [

Min. SOS

$(counter)

];

NEXT i

FOR i = 0 to 9

LET counter = i/10;

LOAD * INLINE [

SOR

$(counter)

];

NEXT i

FOR i = 1 to 30

LET counter = i/10;

LOAD * INLINE [

SUR

$(counter)

];

NEXT i

FOR i = 1 to 15

LET counter = i*25;

LOAD * INLINE [

Max. SUSPD

$(counter)

];

NEXT i

//IN LOAD SCRIPT - if user selects a value from above, then get the max because they can select multiple; otherwise use default values

SET vMinSOS = "IF(ISNULL([Min. SOS]), $(dMinSOS), MAX([Min. SOS]))";

SET vMaxSUSPD = "IF(ISNULL([Max. SUSPD]), $(dMaxSUSPD), MAX([Max. SUSPD]))";

SET vSUR = "IF(ISNULL([SUR]), $(dSUR), MAX([SUR]))";

SET vSOR = "IF(ISNULL([SOR]), $(dSOR), MAX([SOR]))";

//EXPRESSION - works! - [Size], [Heads], [SPD] are direct fields in a table, the return value of 1 or 0 is strictly for reference

=IF(

[Size] >= $(vMinSOS) AND

[Size] - ((([Heads] * IF([SPD] >= $(vMaxSUSPD), $(vMaxSUSPD), [SPD])) / $(vSUR)) + ([Size] * $(vSOR))) >= 0,

1, 0)

//SET ANALYSIS - this needs fixing - i.e. replicate expression above - Show just the results where both the conditions above are true

=SUM({<

[Size]={">=$(=$(vMinSOS))"},

[Size]={">= #### What goes here? #### "},

>}[Size])

Open to recommendations on better ways of solving this.

Re: Set Analysis - Handling complexities - Arithmetic, Fields, Variables, Variables within variables, Greater than etc

petter — Thu, 04 Jan 2018 19:57:31 GMT

I would recommend to try something like this:

=SUM({<UniqueRowID={

"=[Size] >= $(vMinSOS) AND [Size] - ((([Heads] * IF([SPD] >= $(vMaxSUSPD), $(vMaxSUSPD), [SPD])) / $(vSUR)) + ([Size] * $(vSOR))) >= 0"

}>} [Size] )

When doing a search like this it will pick rows from the underlying rowset. That is why you should have a field that is a unique row ID to pick the right rows to be used by the aggregation function. Maybe you already have one - if not create one in the load script. Even just a RowNo() will do as a row id in the right load statement.

Re: Set Analysis - Handling complexities - Arithmetic, Fields, Variables, Variables within variables, Greater than etc

Anonymous — Thu, 04 Jan 2018 20:28:33 GMT

This is great and I do have a unique Id. When I tried what you suggested, SUM of rows that don't satisfy the condition are showing up as well with 0 for value. How can I suppress records with 0 values?

Re: Set Analysis - Handling complexities - Arithmetic, Fields, Variables, Variables within variables, Greater than etc

petter — Thu, 04 Jan 2018 21:59:23 GMT

You have to uncheck "Include Zero Values" in the properties of the chart in the section Add-ons / Data handling