1 Reply Latest reply: Jan 12, 2018 7:28 AM by Henric Cronström RSS

    using COUNT with WHERE and SUM

    Yi Xuan Chia

      Hi,

      I just started qliksense a few weeks ago, and am currently stuck on counting fields with more than 1 criteria, I've searched around in the discussion forums and can't seem to find any solution to this

       

      Basically, I wanted to generate an expression that only shows the my New Customers in 2017, for my targeted products.

      For this, i have thought of an expression that takes in only customers that had bought from us in 2017(invoice amount > 0), and not in 2016(invoice amount=0), for the targeted products, but I cant seem to go about coding the expression for it.

       

      The tables which i currently use is:

      -Invoice details table(which includes all the invoice details of all customers buying, for all products,across years 2015-2017, along with the amount$ paid in the invoice)

      -Targeted Products table(products which we have to look at, which is "Product A", "Product B", "Product C")

       

      The following is the expression which I have done up so far, but instead of showing new customers, it's also showing customers that are not buying from us.

       

      count(

      aggr(

      sum(

      {$<[Invoice Date.autoCalendar.Year]={"2017"},

      [Product-Product Name (Finance File)]={

      "Product A", "Product B", "Product C"

      }

      >}

      [Amount$]

      )

      , CUSTOMER_CODE

      )>0

      AND

      aggr(

      sum(

      {$<[Invoice Date.autoCalendar.Year]={"2016"},

      [Product-Product Name (Finance File)]={

      "Product A", "Product B", "Product C"

      }

      >}

      [Amount$]

      )

      , CUSTOMER_CODE

      )=0

      )