Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Expression query - return 0 if cell is empty

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.

7 Replies
vishsaggi
Champion III
Champion III

Try this in Pivot table.

Dim:

Referrer Name

Prospect Name

Expre:

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

Anonymous
Not applicable
Author

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

vishsaggi
Champion III
Champion III

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])

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Hi ,

use this

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

regards,

Balakrishnan.R

sarasunagaram
Partner - Contributor II
Partner - Contributor II

Hi,

Use this below expression

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

tyagishaila
Specialist
Specialist

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])