6 Replies Latest reply on Dec 15, 2015 11:12 AM by Suman Inampudi

# Help on Calculated field (Like CountIF in Excel)

Hi All,

I need a help on creating a calculated column.

 Qty Code Calculated Qty 100 450362902 100 450362902 100 450362902 100 450362902 100 450362902 210 450362903 210 450362903 210 450362903 210 450362903 210 450362903 210 450362903 210 450362903

In the above set, i have qty 100 for code 450362902 and 210 for 450362903. In the calculated column i would like derive as Qty/No of occurrences for each code.

In the above scenario calculated qty should be 100/5 for each row for 450362902 and 210/7 for 450362903.The output should look like below.

 Qty Code Calculated Qty 100 450362902 20 100 450362902 20 100 450362902 20 100 450362902 20 100 450362902 20 210 450362903 30 210 450362903 30 210 450362903 30 210 450362903 30 210 450362903 30 210 450362903 30 210 450362903 30

Can someone give me an idea on how to achieve this. In excel we do like this Qty / COUNTIF(column range,code) which gives you number of occurrences in that column.

Thanks,

Suman

• ###### 1. Re: Help on Calculated field (Like CountIF in Excel)

Try this:

max([Qty])/count([Code])

• ###### 2. Re: Help on Calculated field (Like CountIF in Excel)

Thank you for looking into this.

I tried the same, but i am getting the same number as result.

When you do max(qty)/count (code) --> i am getting same number: 100/1 = 100, 100/1 =100, 210/1=210 etc....

Am i doing something wrong here?

• ###### 3. Re: Help on Calculated field (Like CountIF in Excel)

If you want the Duplicate rows to be displayed, you may have to add a serial# field to it to make each row unique.

And use a Cal field for your calculation:

[Qty]/{fixed [Qty]: count([Code])} • ###### 4. Re: Help on Calculated field (Like CountIF in Excel)

To get the output you are looking for you'll have to have an ID type field to keep Tableau from aggregating you Codes. Then you'll need to use an EXCLUDE LOD expression to back that ID out to get the math to work: In the future if you post a packaged work book (instead of a table) and mention what version of Tableau you are using, we can better help you. I'm not posting this workbook because it is in 9.2, and I wasn't sure you'd be able to open it.

Cheers,

--Shawn

• ###### 5. Re: Help on Calculated field (Like CountIF in Excel)

See the other replies for the answer.

--Shawn

• ###### 6. Re: Help on Calculated field (Like CountIF in Excel)

Thank you Agasti,

Initially i didn't get right. But when i use this formula it's perfect.

[Qty]/{fixed [Code]: count([Code])}

Instead of qty, i used Code in fixed function

Thanks,

Suman