17 Replies Latest reply: Jun 4, 2012 6:18 AM by Søren Andersen RSS

    Using dimension value to sum records outside of dimension

      I have a problem that should be quite simple to solve, for someone who knows QlikView a little bit better than I do.

       

      I have the following data:

      LOAD * INLINE [
          GroupName, GroupValue, SortOrder, SumGroup

           Turnover, 100, 10, 0

           Cost of goods sold, -50, 20, 0

           Contribution margin 1, 0, 30, 1

           Fuelcosts, -25, 40, 0

           Contribution margin 2, 0, 50, 1

      ];

       

      Now I want a chart with GroupName as the dimension, sorted by SortOrder ascending, with the expression:

      if(only(SumGroup)=1, 'SUMEXPRESSION', Sum(GroupValue))

       

      Instead of SUMEXPRESSION I want a calculation that sums up all GroupValue's with a SortOrder lower than the current dimension value.

      I just can't make an expression that includes the current dimension SortOrder as a variable in the expression.

       

      If I use this expression instead of SUMEXPRESSION:

      Sum(Total if(SortOrder<30,GroupValue,0))

       

      Then Contribution margin 1 shows the correct value (because I have harcoded 30, which is Contribution margin 1's SortOrder). But I want to replace 30 with a function of some sort, that returns 30 for contribution margin 1 and 50 for contribution margin 2, dynamically.

       

      If I use Only(SortOrder) as a separat expression in the chart, I get the correct values, but when I substitute 30 in the above expression for Only(SortOrder) I get null.

       

      What am I doing wrong?