Expression query - return 0 if cell is empty

[Anonymous]

Hi all. I'm very new to this so don't understand how to use the expressions (or how to interpret examples in the help files). I need to return how many "Prospects" by Referrer. A referrer will have multiple rows if they have referrered multiple people. The name of the person is in the "Prospect Name" column. If the referrer hasn't referrered any people the referrer name is in the spread sheet once and the "Prospect Name" column is empty. For these empty cells I am getting "1" as the count for the number of referrals instead of "0". I have ticked "Show Zero Values" under data handling. What do I need to do to get the correct result? I have attached a small sample of the data. Many thanks in advance for your help.

[vishsaggi]

Try this in Pivot table.

Dim:

Referrer Name

Prospect Name

Expre:

= Count(IF(Len(Trim([Prospect Name]))>0, [Referrer Name]))

[Anonymous]

Thank you Vishwarath - that's exactly what I needed 🙂

[vishsaggi]

Did this not answered your query ?

[Anil_Babu_Samineni]

May be alternate approach using Set Analysis

=Count([Prospect Name] = {'$(=Len(Trim([Prospect Name]))>0)'} [Referrer Name])

[#6]

Hi ,

use this

=if(len(trim([Prospect Name]))>0,Count([Referrer Name]),0)

regards,

Balakrishnan.R

[sarasunagaram]

Hi,

Use this below expression

=Count({$<[Prospect Name]={"=len(Trim([Prospect Name]))>0"}>}[Referrer Name])

[tyagishaila]

Try it,

TAB:

LOAD [Referrer Company Name],

    [Referrer Name],

    [Referral Type],

    if(Len(Trim([Prospect Name]))>0,[Prospect Name]) AS [Prospect Name]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Expression: Count([Prospect Name])