2 Replies Latest reply: Dec 20, 2016 5:40 AM by Qlik User RSS

    Help needed: Aggregating Product/Sales Data (Min/Max Dates)

    Qlik User



      We have data in the format below (but over hundreds of thousands of rows):


      Opportunity_IDOpportunity_NameOpportunity_AmountProduct_NameProduct_AmountRevenue Start DateRevenue Close Date
      1Opportunity 1350000Product 12500019/12/201604/05/2017
      1Opportunity 1350000Product 23500019/12/201604/05/2017
      1Opportunity 1350000Product 34000024/12/201604/05/2017
      1Opportunity 1350000Product 41500019/12/201604/05/2017
      1Opportunity 1350000Product 56500019/12/201604/05/2017
      1Opportunity 1350000Product 68000019/12/201604/05/2017
      1Opportunity 1350000Product 75000019/12/201604/05/2017
      1Opportunity 1350000Product 84000019/12/201604/05/2017


      To note,

      • There is not a consistent start date across all products in an opportunity
        • The same with close dates


      The requirement is to output the data in the format:


      Opportunity_NameOpportunity_AmountRevenue Start DateRevenue Close Date
      Opportunity 135000019/12/2016




      Here we are showing the opportunity data at an aggregated level, and showing the earliest corresponding revenue start and close date.


      Attempting to replicate that in Qlik Sense produces the following:

      Min Dates.png

      I don't want to see opportunity 1 appear twice, but just once with the earliest revenue start date. Seeing it twice also means we double count in the totals.


      What is the best way of achieving the required output?


      Please get back to me with any questions and thanks in advance!


      Qlik User