10 Replies Latest reply on Jan 10, 2017 8:41 AM by Brian Connelly

count data based condition with zero in case no record

I have a requirement to show number of units for a particular date each approval status and their value but it should zero if there is no record for approval status. I have attached sheet with record and date selected is 18/12/2016 which has only two approval status. Also I am looking color for circle as fixed red, orange and green. Is it possible?

Thanks

Prakash

• 1. Re: count data based condition with zero in case no record

USE ZN()

• 2. Re: count data based condition with zero in case no record

Here is a link to video that goes over number calculations, but ZN is what you want to use.

• 3. Re: count data based condition with zero in case no record

Hi Prakash,

Find my approach as a starter below and stored in attached workbook version 9.3

• 4. Re: count data based condition with zero in case no record

Hi Norbert,

Thanks for looking on this.

When filter for a particular date e.g. 18/12/2017 it is only showing data for A and B. My requirement is to show C as 0 even there is no data for C.

I used ZN but no success.

Thanks

Prakash

• 5. Re: count data based condition with zero in case no record

I think this is the same problem in this post: Show all, even if NULL? .  It appears that Tableau doesn't have a true function for padding for the null and you will have to do it with the ZN and Look up Function.  I have tested this on the workbook sample from Norbert and it worked ZN(LOOKUP(SUM([Login Count]),0))  .  I've attached the workbook.

1 of 1 people found this helpful
• 6. Re: count data based condition with zero in case no record

Hi Brian,

Thanks for "rounding it up":)

• 7. Re: count data based condition with zero in case no record

Thanks Norbert.

The only remaining problem which has to be handled on the data side is padding so that if a date that is selected which has no values in (say column C), Tableau wont pad and create the column as it does not exist in the returned results. Padding on the data side with a zero would pull it back.

• 8. Re: count data based condition with zero in case no record

Hi Brian,

Realize it's about the "language barrier"...Padding on the data.... Think English and google English and it opens your world;) Padding and Working with Null or Missing Values | Drawing with Numbers

• 9. Re: count data based condition with zero in case no record

Great point....:)  The complexity of a Flat World.