1 Reply Latest reply: Mar 2, 2016 1:02 PM by Stefan Wühl RSS

    Use of "If", "Max" and other sums in an expression

    Kevin Stanfield

      Hi,

       

      I'm struggling combining if, max and other functions to create a comparison of totals for a date (most recent). If the comparison is up/down I want to add an image. This will be easier to explain by the way of example.

       

      Sample data below.

      What I'm trying to achieve is to sum Query_Amount by Query_Status for the date raised or date resolved and subtract the Resolved Query Amount from the Open Query Amount. If the sum is -ve the total amount of Open Query Amount has reduced else it has increased.

       

      Example using the data below:

      Date Raised 17/02/2016 Query Amount with an Open Status = 6,308.39 For Date Resolved 17/02/2016 Query Amount with Query Status of Resolved = 4368.62. The comparison would be 6,308.39 - 4368.62 = 1,939.77 so the Open Query Status would have increased and therefore an image would be shown representing an increase.

       

      In writing this I'm potentially seeing other complications such as if the max date for Date Raised is different from max date for Date Resolved then there wouldn't be a true comparison. Has anyone got any suggestions on dealing with this. One thought is to take a snapshot of the data every time the data is refreshed (daily) and store the total values for Open and Resolved as a new table in QV but I have no experience of doing this. If there a way to create new values stored in a separate table when loading data from a script?

       

      Thanks in advance - Kevin

         

      Date_RaisedDate_ResolvedQuery_AmountQuery_Status
      17/02/201617/02/2016514.66Resolved
      17/02/201617/02/2016800Resolved
      17/02/2016 813.76Open
      17/02/201617/02/20163053.96Resolved
      17/02/2016 455.59Open
      17/02/2016 26.24Open
      17/02/2016 370.64Open
      17/02/2016 69.49Open
      17/02/2016 3554.7Open
      17/02/2016 48Open
      17/02/2016 28.17Open
      17/02/2016 10.02Open
      17/02/2016 13.56Open
      17/02/2016 1264.86Open
      17/02/2016 24Open
      16/02/2016 10Open
      16/02/2016 20Open
      16/02/2016 25Open
      16/02/2016 2345Open
      16/02/2016 765.43Open
        • Re: Use of "If", "Max" and other sums in an expression
          Stefan Wühl

          I would try to create a canonical date in the script:

           

          LOAD

               Date_Raised,

               Date_Resolved,

              If(Status = 'Resolved', Date_Resolved,Date_Raised) as Date,

               Query_Amount,

               Query_Status

          FROM ....;

           

          Then create a chart with dimension Date and as expression

          =Sum({<Query_Status = {'Open'}>} Query_Amount) - Sum({<Query_Status = {'Resolved'}>} Query_Amount)