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

[#1]

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

[sunny_talwar · Accepted solution]

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)

[Anil_Babu_Samineni]

May be this?

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

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

[#4]

thanks, Sunny!!!!