topic Re: count function in QlikView

count function

Wed, 05 Aug 2015 09:02:20 GMT

Hi Experts,

i have the following table.

SALES:

LOAD Branch,

Date,

InvoiceNo,

SalesAmt

FROM

QVD\SALES.qvd

(qvd);

i need to count the invoices whose sales value is above 40.00

there may be a scenario like

Invoice No SalesAmt

01076249 17.95

01076249 10.00

i.e., all the above transactions is on one invoice, so the count should be 1.

i tried using the expresson

Count(DISTINCT if(sum(SalesAmt) > '40.00', InvoiceNo))

but its showing null values.

i dont know where the above expression is wrong.

Please help me on this

Kushal_Chawda — Wed, 05 Aug 2015 09:06:35 GMT

try this

=Count(distinct{<InvoiceNo={"=sum(SalesAmt)>40"}>}InvoiceNo)

sunny_talwar — Wed, 05 Aug 2015 09:07:44 GMT

Try this may be:

=Count({<InvoiceNo = {"=Sum(SalesAmt) > 40"}>}DISTINCT InvoiceNo)

Anonymous — Wed, 05 Aug 2015 09:07:48 GMT

try to cget Count of all InvoiceNo instead of each single transaction

Count(DISTINCT if(aggr(sum(SalesAmt),InvoiceNo) > '40.00', InvoiceNo))

sasiparupudi1 — Wed, 05 Aug 2015 19:32:14 GMT

=Count({<InvoiceNo = {"=Sum(SalesAmt) > 40"}>}DISTINCT InvoiceNo)