4 Replies Latest reply on Aug 11, 2016 12:52 PM by Stoyko Kostov

    If statement using two datasets

    Daniel Kaplan

      Hi everyone,

       

      I'm trying to work out a "per-person" sales number based on a defined seating category. The seating category determines if they are seated on the floor or not. The average bar sales number is total bar sales divided by the number of non-floor tickets while the average floor sales number is floor sales divided by the number of floor tickets. I'm using two data-sets that are similar to the attached excel files.

       

      I tried to use the following statement but can't get it to work. Any help would be appreciated - thank you!

      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

        • 1. Re: If statement using two datasets
          Stoyko Kostov

          Hi Daniel,

           

          In formulas that reference multiple data sources, you are required to aggregate on fields from the non-current data source.

           

          This may help:

           

          IF STARTSWITH(ATTR([Concert code (Ticket sales_example)].[Seating Category]),"2nd")

          THEN SUM([Bar Sales])/(SUM([Concert code (Ticket sales_example)].[Number of tickets]))

          ELSEIF STARTSWITH(ATTR([Concert code (Ticket sales_example)].[Seating Category]),"1st")

          THEN SUM([Bar Sales])/(SUM([Concert code (Ticket sales_example)].[Number of tickets]))

          ELSE SUM([Floor Sales])/(SUM([Concert code (Ticket sales_example)].[Number of tickets]))

          END

           

          ATTR is an aggregation that simply returns the value of the column if it is constant. If it is not, it would return "*".

           

          This formula will require that you use Seating Category as one of the grouping dimensions, in order to achieve the requirement of constant Seating Category.

           

          Take a look at the workbook I'm attaching. Let me know if you have other questions.

          1 of 1 people found this helpful
          • 2. Re: If statement using two datasets
            Daniel Kaplan

            Thanks Stoyko. That was helpful.

            • 3. Re: If statement using two datasets
              Daniel Kaplan

              I tried to group the seating categories into orchestra and non-orchestra, but when I do this the calculation always uses the "Floor sales" number and divides by the number of tickets. For the non-orchestra group I need the calculation to use "Bar sales" and divide this by number of tickets. Do you know what's going on?

              I've attached an illustrative workbook here.

               

              Thanks,

              Dan

              • 4. Re: If statement using two datasets
                Stoyko Kostov

                Hi Daniel,

                 

                You fell into the trap I warned you against - using ATTR on a group where Seating Category is not constant. It evaluated to '*', and that's why the default clause was used.

                 

                What's worse, you can't even compare ATTR to '*' if it is not constant on your group. It will evaluate to False (even though it is displayed as '*' on the view).

                 

                Just make sure to use ATTR if you are certain it would be constant on each partition.

                 

                For your particular grouping, alter Calculation1 to the following and it will work:

                 

                IF STARTSWITH(MIN([Concert code (Ticket sales_example)].[Seating Category]),"2nd")

                THEN SUM([Bar Sales])/(SUM([Concert code (Ticket sales_example)].[Number of tickets]))

                ELSEIF STARTSWITH(MIN([Concert code (Ticket sales_example)].[Seating Category]),"1st")

                THEN SUM([Bar Sales])/(SUM([Concert code (Ticket sales_example)].[Number of tickets]))

                ELSE SUM([Floor Sales])/(SUM([Concert code (Ticket sales_example)].[Number of tickets]))

                END

                 

                MIN() would take the first category in alphabetical order, which in your grouping would start with "1st" for your first group and "Orchestra" for the second. It will work, but be watchful of unintended consequences if additional categories are added to your groups.

                 

                Hope this helps! Let me know if you need more help.

                1 of 1 people found this helpful