10 Replies Latest reply on Nov 7, 2013 11:48 AM by Jonathan Drummey

# calculated field with string output along customer per customer segment

So I've attached a view using the Superstore data that should help me apply it to my real data... hopefully.

It's just a simple crosstab. Rows are customers, columns are Customer Segment / Department.

Here's what's challenging at least for me...

I'd like to be able to see the available connections by segment and department. By available connections I mean where a customer is already bought a product in a segment but hasn't yet bought in all 3 departments. So in that instance I'd like the view to display an A as available, and in that segment where he's already bought a product to display as E for existing.

Take Aaron Bergman for example, the 4th customer down.  He's a Corporate customer, so Consumer, Home Office and Small Business segments should remain blank for all departments, but For Corporate where there is a 10 for Office Supplies and a 4 for Technology, I'd like that to show as E. Then for Furniture under Corporate where he hasn't yet purchased a product, I'd like that to show as 'A'.

If I've been able to explain what I'd like to see clearly, can you help me acheive that output?  I've tried simple IF statements but I'm not able to even come close to what I need.  Missing the boat on this one big time.

• ###### 1. Re: calculated field with string output along customer per customer segment

Hi there--if we could force 0 to appear for the 'A' values, it would be simple:

if attr([Customer Segment])="Consumer"

then (IF sum([Number of Records])>0 then'E'

else 'A' END)

elseif attr([Customer Segment])="Corporate"

then (IF sum([Number of Records])>0 then'E'

else 'A' END)

elseif attr([Customer Segment])="Home Office"

then (IF sum([Number of Records])>0 then'E'

else 'A' END)

then (IF sum([Number of Records])>0 then'E'

else 'A' END)

END

However, this results in a view like:

I couldn't figure out a way to replace the blank values with 0--IFNULL and ZN do not work in this case.  Someone will know what to do, I'm certain of that!  Some Custom SQL to inject values, perhaps, or a Scaffold data source to complete these domains?  Not sure what the best approach is, but its certainly possible.

• ###### 2. Re: calculated field with string output along customer per customer segment

Thanks for your quick help Matthew.  I wasn't able to force the 0s to show either.

I see how you can reference the attribute and have hard coded those in but in my data I have many segments and deparments is there a way to dynamically reference the attribute?

• ###### 3. Re: calculated field with string output along customer per customer segment

How many are we talking?  Because even with 100, it wouldn't take too long since it was mostly copy and paste. There is also a case statement generator available here that could assist:

There may be a better way, I'm not sure.

• ###### 4. Re: calculated field with string output along customer per customer segment

Now that I think of it, it may not only be the number of them that is of concern. It's the fact that new ones can pop up each month.

• ###### 5. Re: calculated field with string output along customer per customer segment

So I was able to force the blanks to show as 0s with this formula I found on another thread:

zn(lookup(sum([Number of Records]), 0))

However the issue still exists as the 0's must just be placeholders and not able to be referenced in a subsequent calculation. I can't use your if statement to produce the expected results. Still just shows E's not A's.

• ###### 6. Re: calculated field with string output along customer per customer segment

I should've known that, I apologize.  I'm gonna ping Jonathan Drummey (who I learned that lookup trick from--I should've ALSO known to try that) as he is very familiar with how Tableau handles this type of scenario.

• ###### 7. Re: calculated field with string output along customer per customer segment

Oh don’t apologize I greatly appreciate you taking the time to help me!

• ###### 8. Re: calculated field with string output along customer per customer segment

See the attached. It takes advantage of the table layout to use a table calculation to trigger domain completion. The table calc first checks to see whether there are any records within the Customer Segment&Customer, then marks each Department in that with either A, E, or Null.

Jonathan

• ###### 9. Re: calculated field with string output along customer per customer segment

Wow! This is exactly what I need!  Thank you both for your time and efforts on my behalf! Now off to apply to my real data....

• ###### 10. Re: calculated field with string output along customer per customer segment

You're welcome, and good luck!