10 Replies Latest reply on Feb 24, 2012 1:27 PM by allysonwinters2

# Case Statement

Hi everyone,

I am trying to create a calculation per division.  There are four divisions, each with it's own nuance.  Division 1 I would like to take the total gross profit for Division 1 and divide by TOTAL company revenue.  The remaining divisions I would like to take a particular branch within that division and divide by the total revenue for just that division.

This case stmt got me part way there:

case attr([Division]) WHEN 'Division 1' THEN attr([Gross Profit])/[Total Company Revenue] else attr([Gross Profit])/attr([Total Revenue]) end

At the division level however, I see astericks for each month and no data for the remaining divisions.

I have attached an excel file to show what the data looks like.

Any help would be greatly appreciated.

• ###### 1. Re: Case Statement

I think those ATTR functions may not be working for you.  I'm not used to seeing those around measures, and if there is more than one value in the data set within the dimension you're in, ATTR returns an asterisk.

If you could add a few more columns to that spreadsheet you could use it as a Tableau data source.  Then you'd be able to post an almost-working packaged workbook to this thread and you'd gets lots of help.

• ###### 2. Re: Case Statement

I worked on this a bit, and here's the excel file I was using (with the extra columns).

--Shawn

• ###### 3. Re: Case Statement

Hi Zach

I had put the attr function to rid myself of the aggregate non-aggregate error.

• ###### 4. Re: Case Statement

Hi Shawn

I created a packaged workbook with the revised file you posted.  I'm not sure how the extra columns will help.

Allyson

• ###### 5. Re: Case Statement

See what you think of this, no ATTR required!  A few more tweaks to the table format will get it to appear as one.

• ###### 6. Re: Case Statement

Allyson, it looks like the TWBX you posted was created in Tableau 6.0 or earlier.  My revision uses 7.0 but doesn't use any 7.0 features.  I could redo it in 6.1 if that helps but I don't have 6.0 or earlier any more.

• ###### 7. Re: Case Statement

Hi Zach,

Yes please.  Hopefully 6.1 will work.

• ###### 8. Re: Case Statement

6.1 WON'T work if all you have is 6.0.  Please let me know before I redo it.  This is a constant problem because forum responders are typically using the latest version while forum questioners are often stuck with an earlier version because they haven't upgraded their production environment.  We're still using 6.1 in production but I'm practicing with 7.0 whenever possible.

UPDATE: I found a copy of 6.0 and am redoing the changes in that version for maximal compatibility.

Workbook attached.

Message was edited by: Zach Leber

• ###### 9. Re: Case Statement

Thanks Zach.  Sorry for the delay.  I will take a look and let you know.

Allyson

• ###### 10. Re: Case Statement

Thanks for your help Zach.  It worked.

Allyson