Get number of invoices over $100 000

[hfkchristian]

Hi all

I start by making some example data. We have a table with invoice lines, like this:

Invoice_Number	Amount
1	$ 80 000
1	$ 50 000
2	$ 120 000
3	$ 10 000
3	$ 20 000
3	$ 10 000
4	$ 70 000

Now I need a text object which displays the number of invoices with a total of at least $ 100 000. In this example 2 (Invoice number 1 and 2). But I can't get it to work in QlikView.

I tried different formulas with the AGGR and SUM functions, and I get all kinds of strange results, but not the correct one. Can someone help me to get the formula correct?

[Anonymous]

try like:

=if(sum(Amount)>'$ 100000', count(distinct(Invoice_Number)))

[tamilarasu · Accepted solution]

Hi,

=Count(DISTINCT{<Invoice_Number ={"=Sum(Amount)>100000"}>}Invoice_Number)

[Anonymous]

or

=if(sum(Amount)>'100000', count(distinct(Invoice_Number)))

Make sure format of Amount should be number

[#5]

Hi christian

try this one

=count(distinct Aggr(sum(amount)>=100000,Invoicenumber))

[hfkchristian]

This works. Thanks

[hfkchristian]

Thanks, but this formula didn't work for me. I used Tamil's soluotion instead.

[anlonghi2]

Hi Christian,

try with this:

=count(if(aggr(sum(replace(Amount,' ','')),Invoice_Number)>100000,1))

Best regards

Andrea

[awhitfield]

Hi Christian,

see attached example, this is using

='Number of Invoices $100 000 and over  ' & Count(DISTINCT{<Invoice_Number ={"=Sum(Amount)>100000"}>}Invoice_Number)

HTH Andy

[tamilarasu]

You are welcome christian.