3 Replies Latest reply on Aug 9, 2016 10:39 PM by Ashish Chaudhari

# Nested calculation in an "IF" statement

Hi Tableau users,

I have ticket sales data that can be divided into floor and balcony seating (based on seating category classification) and drinks sale data that is also be divided into floor and balcony sales. The linking field is concert code. See below.

I want to create calculated fields for "per person" floor and balcony drinks sales which is just the sum of floor/balcony sales divided by the number of floor/balcony tickets.

E.g. To calculate per person drinks sales on the floor, something like:

If <seating category>="Orchestra Price 1" or "Orchestra Price 2" or "Orchestra Price 3" then sum(Floor sales)/sum(Number of tickets)

...

As a follow-up question: Could I group all the "Orchestra" seating categories together and use this group in the IF statement, rather than spelling out individual Orchestra categories?

Would appreciate any help. Thank you!

Drinks sales

 Concert code Balcony Sales Floor Sales Total Sales 2016-06-04 Sat(E)-0604PE 1450.54 5374.18 6824.72

Ticket sales

 Concert code Seating category Number of tickets Value of tickets 2016-06-04 Sat(E)-0604PE 1st Balcony Ct. Price 4 3 216 2016-06-04 Sat(E)-0604PE 1st Balcony Ct. Price 5 14 868 2016-06-04 Sat(E)-0604PE 1st Balcony Lt. Price 4 43 3096 2016-06-04 Sat(E)-0604PE 1st Balcony Rt. Price 4 37 2664 2016-06-04 Sat(E)-0604PE 2nd Balcony Ct. Price 6 8 360 2016-06-04 Sat(E)-0604PE 2nd Balcony Ct. Price 7 0 0 2016-06-04 Sat(E)-0604PE 2nd Balcony Jump Seats Price 8 0 0 2016-06-04 Sat(E)-0604PE 2nd Balcony Lt. Price 6 38 1710 2016-06-04 Sat(E)-0604PE 2nd Balcony Rt. Price 6 32 1440 2016-06-04 Sat(E)-0604PE Orchestra Price 1 39 4680 2016-06-04 Sat(E)-0604PE Orchestra Price 2 53 5035 2016-06-04 Sat(E)-0604PE Orchestra Price 3 66 3335
• ###### 1. Re: Nested calculation in an "IF" statement

Hi Daniel,

If STARTSWITH([Seating category],"2nd")=TRUE then [Balcony Sales]/[Number of tickets]

ELSEIF STARTSWITH([Seating category],"1st")=TRUE then [Balcony Sales]/[Number of tickets]

ELSEIF  STARTSWITH([Seating category],"Orchestra")=TRUE then [Floor Sales]/[Number of tickets]

END

Let me know if that helps.

-Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 2. Re: Nested calculation in an "IF" statement

Thanks for your help Ashish! I still can't get it to work somehow though (I keep getting an error about combining aggregate and non-aggregate data in an IF statement). I've attached an expanded set of excel files to give you a better idea of the data I'm working with (they are separate files so I tried to link them using the common concert code). I tried the following:

If STARTSWITH([Ticket Sales ].[Seating Category],"2nd")=TRUE then sum([Bar Sales])/sum([Ticket Sales ].[Number of tickets])

ELSEIF STARTSWITH([Ticket Sales ].[Seating Category],"1st")=TRUE then sum([Bar Sales])/sum([Ticket Sales ].[Number of tickets])

ELSE sum([Floor Sales])/sum([Ticket Sales ].[Number of tickets])

END

If you could take a look that would be much appreciated!

Daniel

• ###### 3. Re: Nested calculation in an "IF" statement

Hi Daniel,

by looking at your field, I believe you have not using these two databases separably. By that I mean you have added two separate data-source rather than combining the two sheet as a join by using join. I have highlighted the calculation part which indicates me that blending is done instead of using joins.

If STARTSWITH([Ticket Sales ].[Seating Category],"2nd")=TRUE then sum([Bar Sales])/sum([Ticket Sales ].[Number of tickets])

ELSEIF STARTSWITH([Ticket Sales ].[Seating Category],"1st")=TRUE then sum([Bar Sales])/sum([Ticket Sales ].[Number of tickets])

ELSE sum([Floor Sales])/sum([Ticket Sales ].[Number of tickets])

END

I am looking into excel files that you just attached.

-Ashish

1 of 1 people found this helpful