2 Replies Latest reply: Jul 6, 2012 11:36 AM by melaniehuggins RSS

    Trouble with an expression



      Continuing my love hate relationship with QlikView I can't seem to work out something....

      Basically I'm loading in a data like this


      Site1, CCS1, dd/mm/yy

      Site1, CCS2, dd/mm/yy



      then adding up all the scores to give me a total which is then multipled to give a financial value - hopefully you can see that on the attached example.


      I've managed to work out a figure that gives me the cost of non-compliance (shown on table as Prediction minus Subsistence £13,132.35) but I can't seem to replicate this in a text box to make the figure stand out - how on earth can I copy this expression without showing all the individual site totals.



        • Re: Trouble with an expression
          Stefan Wühl

          You should be able to do this using advanced aggregation, something like


          =sum( aggr( YOUREXPRESSION, Site))


          But, YOUREXPRESSION is really complex in your case, since you are referencing another expression in your column expression which is referencing another expression, which is referencing ....


          You could try embedding all these expressions into one biiiig expression, or maybe try simplyfying your expressions (maybe by precalculating as much as possible in the script).


          Hope this helps,


            • Re: Trouble with an expression

              Thanks, as I'm only a new to QV I think that is going to be too advanced for me at the moment, but I was thinking along the same lines which is promising.


              Anyway to solve the problem, I've created a duplicate table, hidden all the columns except site name and cost, limited the number of rows to 3 and sorting show it shows the top 3 sites as well as the total cost of non-compliance.

              I think this will work


              Can I do a set analysis on this expression, so I can show the total regardless of the selection



              Rank (TOTAL(cost))