Hi all,
I'm working on a dashboard and I'd like your help in creating a data item that takes the last 5 records of a table (the last 5 dates) and adds up another field in the table.
ex:
Date Value
2011/03/31 25.00
2011/03/30 25.00
2011/03/29 25.00
2011/03/28 25.00
2011/03/27 25.00
2011/03/26 25.00
The dataitem should bring up 125.00 as a total.
Can you help?
Thanks!
CC
Is it a relational model? If so, take a look at the rank function. Use that to identify the latest five based on the date (such that the 5 you want show up with ranks 1 through 5).
Then you can interrogate the rank value to derive the proper total. Create a query item with the expression something like this:
if ( [RankValue] <= 5 ) then ( [Value] ) else ( 0 )
Good luck!
Thanks, that worked exactly like I wanted it to :D
Maybe there is a small problem after all...
my rank function is skipping records!
My data looks like this:
DATE LINE VALUE
20110428 11 0
20110429 11 0
20110502 11 25
20110503 11 35
20110504 11 125
and my rank function: rank([DB].[Tablename].[Field])
result comes up like this:
DATE VALUE LINE
4 0 11
3 125 11
1 25 11
2 35 11
Rank 5 is missing, and when I remove my ranking filter, I see a bunch more missing, yet I see the data in the table. :-\
It's skipping lines with a value of 0... could it be a duplicate value issue? I've read about a dens_rank function but it doesn't seem to work in RS.
Any ideas?
Oh jeez, I just found the rank(distinct) function.
The Rank (without the distinct) should be fine but you have to set a filter in the query to say Rank <= 5.
Make the filter a Summary filter and the Rank data item should be set to 'after aggregation'.