2 Replies Latest reply on Jan 29, 2018 8:04 AM by Jim Dehner

    Is there a way to combine aggregate measures from distinct data sources?

    Mark Bashuk

      I am trying to migrate reports from Qlik Sense - and I'm not able to (or don't know how) to easily combine aggregate metrics from data sources that don't have a natural join.

       

      In this example I'm trying to project where a revenue metric will end up. I want to take the sum(revenue) from the actual_sales table and add it to the sum(projected) value from project_revenue table to get a projected_final number..

       

      A single customer can't be in both tables - I don't know the correct way to join -I thought about adding a "join' column to connect the data sources, but that didn't seem like a good idea either - it would work like a cross join.

       

      If you check out the attached workbook, I can get both metrics on the same table, but I can't total them up.

       

      Am I missing something obvious?

       

      Any input is appreciated.

       

      Thanks - Mark

        • 1. Re: Is there a way to combine aggregate measures from distinct data sources?
          Isaac Kunen

          Hi Mark,

           

          I'm having trouble getting your .twbx to work, but it feels like you might want to do a union in the data source rather than a join.

           

          -Isaac

          • 2. Re: Is there a way to combine aggregate measures from distinct data sources?
            Jim Dehner

            Hi Mark

             

            working with the data in the present structure you have 2 data sources that you blended together -

            to get a total you need to bring the 2 measures together into a single viz that is associated with one of the sources from the secondary source-

            The first sources you work with is the primary - when you bring data from the secondary it comes over as an aggregate so you need to create an aggregation like the one shown below -note this is a new measure not a Grand Total

             

             

             

            then

            you could create a viz like this

             

            another solution is available if you could restructure the data to look like this

             

                 

            Source 1
            Acct #Price LevelProductlevelamount
            321DugoutQuarter SeasonUnrenewed Revenue$10,000
            432FieldHalf SeasonUnrenewed Revenue$7,500
            543DugoutFull SeasonUnrenewed Revenue$2,500
            654Home PlateFull SeasonUnrenewed Revenue$1,225
            Source 2
            Acct #Price LevelProductlevelamount
            123DugoutFull SeasonRevenue$10,000
            456FieldFull SeasonRevenue$5,000
            789DugoutHalf SeasonRevenue$5,000
            135Home PlateQuarter SeasonRevenue$5,000

             

             

            This data can be combined in a Union (like appending data) in a single data file (source) and can be directly summed, sub-totalled etc

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.