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

    Nested calculation in an "IF" statement

    Daniel Kaplan

      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 codeBalcony SalesFloor SalesTotal Sales

      2016-06-04 Sat(E)-0604PE

      1450.545374.186824.72

       

      Ticket sales

      Concert codeSeating categoryNumber of ticketsValue of tickets
      2016-06-04 Sat(E)-0604PE1st Balcony Ct. Price 43216
      2016-06-04 Sat(E)-0604PE1st Balcony Ct. Price 514868
      2016-06-04 Sat(E)-0604PE1st Balcony Lt. Price 4433096
      2016-06-04 Sat(E)-0604PE1st Balcony Rt. Price 4372664
      2016-06-04 Sat(E)-0604PE2nd Balcony Ct. Price 68360
      2016-06-04 Sat(E)-0604PE2nd Balcony Ct. Price 700
      2016-06-04 Sat(E)-0604PE2nd Balcony Jump Seats Price 800
      2016-06-04 Sat(E)-0604PE2nd Balcony Lt. Price 6381710
      2016-06-04 Sat(E)-0604PE2nd Balcony Rt. Price 6321440
      2016-06-04 Sat(E)-0604PEOrchestra Price 1394680
      2016-06-04 Sat(E)-0604PEOrchestra Price 2535035
      2016-06-04 Sat(E)-0604PEOrchestra Price 3663335
        • 1. Re: Nested calculation in an "IF" statement
          Ashish Chaudhari

          Hi Daniel,

           

          This will solve your problem.

          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
            Daniel Kaplan

            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
              Ashish Chaudhari

              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