1 Reply Latest reply on Jun 6, 2014 9:21 AM by Zac Hilbert

    Translate from excel to Tableau

    Fred Dais

      I am trying to translate a excel expression into Tableau. I want to count the number of pass, fail, corr and come up with a percentage complete . Any feedback would be great

      Excel expression

      =IF(T138="n/a","n/a",IFERROR((COUNTIF(M138:Q138,"pass")/(COUNTIF(M138:Q138,"pass")+COUNTIF(M138:Q138,"fail")+COUNTIF(M138:Q138,"corr"))), ""))=IF(T138="n/a","n/a",IFERROR((COUNTIF(M138:Q138,"pass")/(COUNTIF(M138:Q138,"pass")+COUNTIF(M138:Q138,"fail")+COUNTIF(M138:Q138,"corr"))), ""))

        • 1. Re: Translate from excel to Tableau
          Zac Hilbert

          Fred,

           

          It will be difficult for any one to help you out with a specific solution because

          1. We do not know what your Excel data look like
          2. We do not know what your Tableau data look like
          3. We do not know what you want your Tableau visualization to look like.
          4. Your description and Excel formula are unclear and do not tell us exactly what you are trying to compute.

           

          I recommend posting a packaged workbook and your Excel data. And make sure your Excel formula is correct--it doesn't look like a valid Excel formula, and the parts that do look valid have some odd things like dividing numbers by the same which would always be 1. It will be easier for someone to help you out if you can provide more info. I also recommend checking out this document:  Posting a Perfect Question.

           

          That aside, I am going to make some assumptions based on your Excel data and guess that the solution will involve some calculated fields that look like this:

           

          Passes = if columnM="pass" then 1 else 0 end  + if columnN="pass" then 1 else 0 end + ... if columnQ="pass" then 1 else 0 end

          Fails = if columnM="fail" then 1 else 0 end  + if columnN="fail" then 1 else 0 end + ... if columnQ="fail" then 1 else 0 end

          etc.

           

          And you'll need some table calculations, e.g.

           

          TotalPasses = total(sum(Passes)

           

          And then get a percentage by dividing:

          Pct = Passes / TotalPasses.

           

          Hope this helps.

           

          Zac