Solved: Set analysis last 5 qualify date range - Qlik Community

Report Inappropriate Content · ‎2013-02-07

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

Report Inappropriate Content · ‎2013-02-07

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}).

View solution in original post

Report Inappropriate Content · ‎2013-02-07

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}).