count function

[#1]

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      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]

try this

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

[sunny_talwar]

Try this may be:

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

[Anonymous · Accepted solution]

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

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

[sasiparupudi1]

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