3 Replies Latest reply on Jun 21, 2016 12:48 PM by P N

    Calculated Field: Sum of Product of 2 Blended Dimensions

    Kanishka Rao

      Hello,

       

      I have 2 datasets I've blended such that with my filtered view there is a one-to-one match of the number of products sold and the price of each product. My calculated field is SUM(Quantity)*Sum(Price). However, when I want to see total revenue for a day, I am getting gross overestimates in the aggregation as it is summing all the quantities of all the different products, and the sum of all prices.

      Simply put, I would like to instead see SUM(Quantity*Price) for each date. Is it because I'm blending two data sources that it's not letting me link my price database with my quantity database in a single expression?

       

      My data looks like

       

      Date     Item     Quantity     Price

      6/16     Chalk     5               9

      6/16     Food     4               12

       

      What I would want to see is 5*9 + 4*12 when I see filter to each date, but instead I am seeing (5+4)*(9+12) which is very very different.

       

      For this data I see:

      Date     Revenue

      6/16     189

       

      But I want:

       

      Date     Revenue

      6/16     93

       

      Any help would be greatly appreciated.

        • 1. Re: Calculated Field: Sum of Product of 2 Blended Dimensions
          P N

          Try to use SUM(Quantity*Price) for the calculated field instead of SUM(Quantity)*Sum(Price).

          • 2. Re: Calculated Field: Sum of Product of 2 Blended Dimensions
            Kanishka Rao

            I get the error "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources"

            • 3. Re: Calculated Field: Sum of Product of 2 Blended Dimensions
              P N

              Hi Kanishka:

               

              With data blend you can't create table calculations without aggregation. I have tried my hand at finding the solution though there might be something better. The 2 example datasets that I selected were:

               

              Dataset 1                                                                 Dataset2

              DateItemQuantityDateItemPrice
              6/16 Chalk56/16 Chalk9
              6/16 Food46/16 Food12
              7/16  Fruits27/16  Fruits3

               

              As you can't combine table calculations without aggregation, the solution lies in starting at the most granular level of blend i.e Date & Item to calculate the Revenue. Here are the steps:

               

              1) Create a calculated Field Revenue which is sum(Quantity)*sum(Price)

              2) Create the table calculation 'Running Total of the Revenue' as shown in the screenshot below.

              Select Advanced in the Running Along.

              Select Date in the Restarting Every.

               

              The last row of your Date/Item combination contains the answer you are looking for.

               

              3) To keep only the last row create a second calculated field Whether Last or not using the Last() function and selecting the default table calculation (blue font) with the same selections as Running total above i.e, Advanced selecting Date & Item and select Date in the Restarting Every.

               

              4)  Put it in the filter shelf and select At Most value as 0.

              5) You can hide the other fields except date by deselecting the show header as shown below. It also shows the solution:

               

              Thanks

              1 of 1 people found this helpful