0 Replies Latest reply on May 17, 2013 2:03 PM by amy.dombrowski

    counting multiple fields as one in a calculation

    amy.dombrowski

      Hello,

      We have the following data.  We are trying to figure out the following:

       

      There are 2 distinct quote numbers below that have multiple records in the table.  We need to include all rows in our data, without applying any filters, yet want to only calculate some rows that have repeating data for each record. 

       

      For instance, Quote App Submitted Indicator.

      If we were to SUM this field, for this data, we would want 2 not, 7, 1 for each distinct quote number.

       

      We would want this concept applied for filtered/sliced views that change the amount of rows per quote number. 

       

      For Example, Quote Number 22,626,850 has 4 rows in our table, but if we filtered for Vehicle Value of $2,500, he would have 3 rows.  We still would want the sum of Quote App Submitted Inidcator to = 1 in both cases. 

       

      We've tried to create a calculated field where the Quote App Submitted Indicator was 1/count distinct of rows for each Quote Number.  But, that was not working. 

       

      Any help would be appreciated

       

      Quote App Submitted IndicatorQuote NumberQuote Stock Vehicle CountQuote TIVVehicle Value
      122,622,850411,0002,500
      122,622,850411,0003,500
      122,622,850411,0002,500
      122,622,850411,0002,500
      12258188939200036000
      12258188939200036000
      12258188939200020000