Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
12021656627z
New Contributor II

using COUNT with WHERE and SUM

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

)

1 Reply

Re: using COUNT with WHERE and SUM

Maybe

Count(

   {$<

      Product={'Product A','Product B','Product C'},

      Customer=E({$<Customer={"=Sum({$<Year={2016}>} Amount)>0"}>} Customer)

   >}

   distinct Customer)

Chart.png

HIC