Count no of transactions within a value range

[#1]

Hi

I’m new to Qlik and am trying to  count the number of transactions where the total transaction amount is with a value range eg < 1000.

Eg

Transaction         Item      Amount

abc                          1                400

abc                          2              1200

abc                         3                400

def                          1                100

def                        2                200 

Expected  Result

Tansaction     Count(< 1000)

abc                           0

def                             1

I’ve tried the following expressions

  count({$<[Amount]={'>0<=1000'}>} Distinct Transaction)//count(DISTINCT [Transaction])

  this is not working as it evaluates each item of the transaction>

  eg it returns the result "1" for transaction abc  as the items 1 and 3 are  less than 1000.

This expression returns total of the  whole document

sum(DISTINCT TOTAL < [Transaction]> [Amount] )

ie  abc = 2000 and def = 300

I’ve tried to incorporate the sum expression into the count expression but haven’t been successful.

’ve also tried various if statement without success.

Any suggestions would be appreciated.

Thanks

[Digvijay_Singh]

Try this -

count({$<Transaction=e({<Amount={">1000"}>}Transaction)>} Distinct Transaction)

[Digvijay_Singh]

So we are excluding transactions where any of the amount is >1000 in the count.

[sibusiso90]

I am not sure what you are looking but this is what I have

if(sum(Amount)<1000,1,0) which produces what is shown below.

[#5]

Hi

Thanks for the replies.

When I tried the if statement  and it works except when the transaction is removed form the dimension.

The total is zero.

When I tried the following expression it seem to apply to the item rather than the total,

count({$<Transaction=e({<Amount={">1000"}>}Transaction)>} Distinct Transaction)

[Digvijay_Singh]

Cannot open qvw due to personal edition, so you want total amount of transaction id  shouldn't be >1000, try after replacing Amount with sum(Amount).

[jonathandienst]

The problem is you are counting Distinct and using the transaction as a dimension. This will always return 1. Remove the distinct so it counts rows, and add a TOTAL to the second clause (I assume you want to divide by the total number of rows):

=Count({$<[Amount]={">0<=1000"}>} Transaction) / count(TOTAL [Transaction])

[Anil_Babu_Samineni]

May be this?

If(Sum(TOTAL <Transaction> Amount)<1000, Count(DISTINCT Transaction),0)

Then suppress when value is null from presentation Tab

[antoniotiman · Accepted solution]

Hi Ingrid,

Try

Count(Aggr(If(Sum(Amount) < 1000,Transaction),Transaction))

Regards,

Antonio

[#10]

Hi Antonia

this expression worked for me .

Thanks you.

Ingrid