Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Ingrid,
Try
Count(Aggr(If(Sum(Amount) < 1000,Transaction),Transaction))
Regards,
Antonio
Try this -
count({$<Transaction=e({<Amount={">1000"}>}Transaction)>} Distinct Transaction)
So we are excluding transactions where any of the amount is >1000 in the count.
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.
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)
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).
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])
May be this?
If(Sum(TOTAL <Transaction> Amount)<1000, Count(DISTINCT Transaction),0)
Then suppress when value is null from presentation Tab
Hi Ingrid,
Try
Count(Aggr(If(Sum(Amount) < 1000,Transaction),Transaction))
Regards,
Antonio
Hi Antonia
this expression worked for me .
Thanks you.
Ingrid