Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Count transactions with amount less than/equal to min threshold and greater than/equal to max threshold

Hi

I have 2 thresholds and summary tables that show the amount of transactions less than/equal to the min threshold as well as the sum of amount for those transactions. Similarly, I have a summary table that shows the count of transactions greater than/equal to the max threshold as well as the sum of the amount field for those transactions.

I want a KPI table that shows me ALL selected transactions (the count of transactions equal to the thresholds as well as less than and greater then the thresholds respectively.

For example:

Min Threshold- 100

Max threshold- 1000

transactions

id   amount

1     100   

2     80    

3     500

4     1000

5     5000

Count of transactions: 4 (id's 1,2,4,5)

sum of amount: $6,180

What calculation can I use for this? I'm thinking some form of count( {<amount={$(vMinThreshold),$(vMaxThreshold)}>} id) but i need to add in the <= and >=.

Thanks!!!

Laila

1 Solution

Accepted Solutions
Highlighted

Re: Count transactions with amount less than/equal to min threshold and greater than/equal to max threshold

May be this

Count(DISTINCT {<ID = {"=Sum(amount) <= vMinThreshold or Sum(amount) >= vMaxThreshold"}>} ID)

Or if a single id belongs to only one amount, then this

Count(DISTINCT {<amount = {"$(='<=' & vMinThreshold)"} + {"$(='>=' & vMaxThreshold)"}>} ID)

View solution in original post

3 Replies
Highlighted

Re: Count transactions with amount less than/equal to min threshold and greater than/equal to max threshold

May be this

Count(DISTINCT {<ID = {"=Sum(amount) <= vMinThreshold or Sum(amount) >= vMaxThreshold"}>} ID)

Or if a single id belongs to only one amount, then this

Count(DISTINCT {<amount = {"$(='<=' & vMinThreshold)"} + {"$(='>=' & vMaxThreshold)"}>} ID)

View solution in original post

Highlighted

Re: Count transactions with amount less than/equal to min threshold and greater than/equal to max threshold

May be this?

1) Count({<id -= {3}>} id)

2) Money(Sum({<id -= {3}, amount = {">=$(vMinThreshold) <=$(vMaxThreshold)"}>} amount), '$#,##0')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Not applicable

Re: Count transactions with amount less than/equal to min threshold and greater than/equal to max threshold

thanks, Sunny!!!!