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 |
