Hi guys,
I'm hoping you can help me with this. I'm building an application that allows the end user to specify a 'From' and To' date using the Input box object which then drives the period the transaction amounts need summing up. However the requirement is that it needs to only bring back the earliest transaction amount for a category within the user specified date range (From and To dates).
For example:
| From | 15/01/2015 | = vMinTnxDate |
| To | 15/02/2015 | = vMaxTnxDate |
| Dimension | Tnx Date | Amount | |
| A | 15/01/2015 | £10 | First Record |
| A | 16/01/2015 | £20 | |
| A | 17/01/2015 | £30 | |
| A | 18/01/2015 | £40 | |
| A | 19/01/2015 | £50 | |
| A | 20/01/2015 | £60 | |
| A | 21/01/2015 | £70 | Last Record |
| B | 18/01/2015 | £80 | First Record |
| B | 19/01/2015 | £90 | |
| B | 20/01/2015 | £100 | |
| B | 21/01/2015 | £110 | |
| B | 22/01/2015 | £120 | |
| B | 23/01/2015 | £130 | |
| B | 24/01/2015 | £140 | |
| B | 25/01/2015 | £150 | Last Record |
My current expression in a Straight Table is:
Sum({<[Tnx Date] = {'>=$(vMinTnxDate) <=$(vMaxTnxDate)'}>} Amount)
This works so far as dynamically summing up the amount for each dimension within the date range. What I need the expression to do is take the records within the user date range and then just show the amount in the first record for each category (A & B) in this case £10 for category A and £70 for category B. and in a separate column show the latest amount for the respective categories.
I have come across this expression which works to an extent:
=Sum(TOTAL <Dimension>
Aggr(
If([Tnx Date] = Min(TOTAL <Dimension> [Tnx Date]),Amount),
Dimension,[Tnx Date])
)
Any help with this would be appreciated,
Many thanks
