3 Replies Latest reply: Jan 4, 2018 5:00 PM by Petter Skjolden RSS

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

    Bhanu Kothuri

      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.