Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set of data that i want to sum the range of values between the dates of the last 5
In my examaple i want to sum the values between the last 5 Qualifying dates and compare to the rest of the set of values .
Ex
I need to sum between 2/1/2013 and 1/14/2013 because these are the range where the last 5 qualify equal 1 are in between. How in set analysis can i Accomlish this
Try this:
=sum({<Date={"=rank(total only({<Qualify={1}>} -floor(Date)))<=5"}>} Value)
This is ranking by the date (or the numeric Date value), it's -floor (negative of the value) so that most recent dates are ranked lower.
We add the Qualify=1 to restrict which we get in the ranking.
Then we sum the Value.
This of course respects current selections (since {$} is assumed), but you can have it calculate overall disregarding selections if you want (using {1}).
Try this:
=sum({<Date={"=rank(total only({<Qualify={1}>} -floor(Date)))<=5"}>} Value)
This is ranking by the date (or the numeric Date value), it's -floor (negative of the value) so that most recent dates are ranked lower.
We add the Qualify=1 to restrict which we get in the ranking.
Then we sum the Value.
This of course respects current selections (since {$} is assumed), but you can have it calculate overall disregarding selections if you want (using {1}).