Qlik Community

Qlik Sense App Development

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

cbrmqlik
New Contributor

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

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

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.

3 Replies
MVP
MVP

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

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.

cbrmqlik
New Contributor

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

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?

MVP
MVP

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

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

Community Browser