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

[Anonymous]

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.

[petter · Accepted solution]

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.

[Anonymous]

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?

[petter]

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