2 Replies Latest reply on Mar 18, 2013 6:29 AM by dale sharkey

    Count records if 2 conditions are met from calculated field that has been duplicated?

    dale sharkey

      Hello,

       

      I am trying to count number of accts by TerrSalesManagerName if Volume2013 is greater than 10 and Volume2012 is less than 500.  I'm using SQL statement to bring in all data, and for Volume, I'm duplicating it and using a calculated field IF [Fiscal_Year] = 2012 THEN [Volume] ELSE 0 END to get my Volume for 2012, and likewise for 2013.  When I do the following calculation:

       

      IF FIRST()==0 THEN

          WINDOW_SUM(sum(IF ([Volume2013]>20 and [Volume2012]<100) THEN 1 ELSE 0 END))

      END

       

      I end up with 7 records, even though I should have 3.  See table below.  But when I do the following calculation:

       

      IF FIRST()==0 THEN

          WINDOW_SUM(sum(IF ([Volume2013]>20 and [AGM2013]<500) THEN 1 ELSE 0 END))

      END

       

      I end up with 6 records which is correct as it is correctly calculated acct's with both conditions.  The problem I believe is when I am splitting the Volume field into Volume2012 and Volume2013.  For some reason, Tableau is not recognizing the 2nd field in the window sum formula due to the Fiscal_Years being different, as it only returns the value of the 1st condition, which is [Volume]>20.  Because when I did the 2nd window_sum calcuation noted above based on AGM2013, it worked because the Fiscal Years condition were the same.  I split AGM into AGM2012 and AGM2013 using similiar formula as shown in 1st paragraph.

       

      Can anyone offer assistance?  Please let me know if I need to provide my detail!

       

      Acct #TerrSalesManagerNameAGM2012AGM2013Volume2012Volume2013
      8533964DANNA S COX2,248651627236
      8582297PAUL A CORBIN6171859736
      8440598BRADLEY M GRICE4861958130
      8543014DANNA S COX74117811828
      8480389PHILIP C ROY1031672126
      3801256LARRY C. CROFT99616215425
      6500594ZANETA J YOUNG1,00515618324
      7469691DANNA S COX12126220
      8571185PHILIP C ROY2131233319
      6186678ERIC L HEITMAN4601137118
      6074467ERIC L HEITMAN5061107817
      8558593BRADLEY M GRICE0111017
      8563561HAL F PRUITT JR.1351032116
      8578447PAUL A CORBIN641021016
      4600173PHILIP C ROY253954015
      8476896BRADLEY M GRICE414987215