Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think the function FirstSortedValue() will help you. Take a look.
try with a str chart
Dimension
FirstSortedValue({<[Tnx Date] = {'>=$(vMinTnxDate) <=$(vMaxTnxDate)'}>} total <Dimension> Amount, -[Tnx Date])
FirstSortedValue({<[Tnx Date] = {'>=$(vMinTnxDate) <=$(vMaxTnxDate)'}>} total <Dimension> Amount, +[Tnx Date])
Hi Michael,
many thanks for the quick response, I will try your proposed solution. With regards to efficiency, does the FirstSortedValue function cope well with high volumes i.e. when we hit the million mark transactions/records? just wondering what consideration I would need to take when it's scaled out.
Many thanks
a million usually isn't a high volume
tries to mimic (makes some test data) the volumes that you will have in a few years
Thanks Massimo
If I have to include more than one dimension, is it simple as adding it within the dimension list just after the TOTAL function.