2 Replies Latest reply: Jul 3, 2018 5:35 AM by Jamie Lim RSS

    Min date across all Item dimension where sum amount by item is not zero

    Jamie Lim
      CompanyItemDateAmount
      CO1A1 Jun 2018100
      CO1A1 Jun 2018-100
      CO1B2 Jun 2018100
      CO1B2 Jun 2018-50
      CO1C4 Jun 2018100
      CO2A5 Jun 2018200

       

      I have the above table loaded in my data load script.

       

      Using Qlik Sense, I will like to create a table that gives me the company, the min date for each Company only for those item whose sum of amount is not zero, and the sum of the amount for each company. In the table above, item A for CO1 will be disregarded in the Min date computation as the sum of amount for Item A under CO1 is zero. The result i will like to achieve is:

      CompanyMin (Date)Sum(Amount)
      CO12 Jun 2018150
      CO25 Jun 2018200

       

      Appreciate any advise on this as I cannot seem to get CO1 to reflect min date of 2 June 2018. It always picks up 1 June 2018.

       

      Thanks!