Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
May be this?
1) Count({<id -= {3}>} id)
2) Money(Sum({<id -= {3}, amount = {">=$(vMinThreshold) <=$(vMaxThreshold)"}>} amount), '$#,##0')
thanks, Sunny!!!!