17 Replies Latest reply: Feb 17, 2015 6:49 AM by Andre Toerien RSS

    Complicated sum - if or set analysis and syntax



      I have the following sum I need to do on a chart. What would be the best way to do this. My gut feel says set analysis but I can't see an example of something like this on the forums

      This is it in SQL basically:



      If TransType not in (6,7) and TransType = 8 and TransSubType in(1,2,3,4) sum  either KmUnit or Hours depending on which has a value. Only one of the two will have a value

      Else  If TransType not in (6,7) sum Quantity


      So TransType 6 and 7 always need to be ignored in the sums - this is where I think set analysis is the way to go.

      The basic sums is not hard doing but the combination is an issue, maybe I am over complicating it. Any advice especially with the syntax would be great