Calculated Field Type and Tableau Server Datasource

[john_miller9]

Hoping for some community expertise to help me tackle a problem.  I have transactional applicant data (Apply, Interview, Offer, Offer Accepted, Hire) in a dataset so several records per person.  I have created a measure for each of these transactions that will create a '1' for that stage (IF [Stage] = 'Offer' THEN 1 ELSE '' ENDIF).  Since I want these to be measures once i publish to tableau server, i have to convert from string to interger with the following formula (ToNumber(IF [Stage] = 'Offer' THEN 1 ELSE '' ENDIF)).  

I also have a formula to calculate Time to Offer Accept which uses the same logic.  However, once i convert to an integer (again, so that it appears as a measure in my published data source), i now have a bunch of Zero's.  This cramps my style because the measure's default aggregation in Tableau is an Average and shouldn't count the zero's but i can't figure out how to do this in Alteryx so that: 

1. There are no zero's in my field

2. The field is recognized as a measure in the published data source.

Obviously with Tableau desktop i can leave it as a string formula in Alteryx and manually change the data type to measure.  But in Tableau server, in order to protect the metadata, this kind of tinkering isn't allowed.  Anyone have any thoughts?

Person	Stage	Apply	Interview	Offer	Offer Accepted	Hire	Time to Offer Accept 1	Time to Offer Accept 2
Joe	Apply	1	0	0	0	0		0
Joe	Interview	0	1	0	0	0		0
Joe	Offer	0	0	1	0	0		0
Joe	Offer Accepted	0	0	0	1	0	22	22
Jill	Apply	1	0	0	0	0		0
Jill	Interview	0	1	0	0	0		0

[JohnJPS · Accepted solution]

Hi John,

Could you use Null() instead of the Empty string?  As in, when preparing the data for Tableau, ensure the field is specified as an Integer data type, but then have your formula be something like IF [Stage] = 'Offer' THEN 1 ELSE Null() ENDIF. I'm not sure how Tableau will treat it; but from the Alteryx angle you will get rid of the zeroes.

[john_miller9]

Your solution was simple AND glorious.  Learn something new everyday.  Thanks!