Help with expression : Avg of latest 7 cases

[vishnus85]

Hi,

We have a measure field as "Duration" for each "Case_number". We have another field named "Case_Date" that would allow us to sort the cases based on the its occurrence date.

My requirement is to find the Avg(Duration) for the latest 7 Cases ( based on Case_Date) for a particular selection.

Can anyone help me frame this expression

Regards,

Vishnu S

[swuehl]

Maybe like

=RangeAvg(

FirstSortedValue(Duration, -Case_Date,1),

FirstSortedValue(Duration, -Case_Date,2),

FirstSortedValue(Duration, -Case_Date,3),

FirstSortedValue(Duration, -Case_Date,4),

FirstSortedValue(Duration, -Case_Date,5),

FirstSortedValue(Duration, -Case_Date,6),

FirstSortedValue(Duration, -Case_Date,7)

)

[vishnus85]

swuel,

That should work. But there might a better solution, one which doesnt need the statemenst to be repeated.

[#4]

Hi,

     it can be done in load script itself.

can you please attach your sample file here. So it will be easy to understand the problem.

Thanx,

Ashutosh

[tresesco]

Try something like:

=Avg(Aggr(If(rank(Case_Date)<8,Duration),Case_Date))

[#6]

If you are working in a chart, use something like this:

RangeAvg(Above(Sum(x),0,7))