rangeavg or set analysis

[Anonymous]

Hi -

I have some data in this form:

date	id	fixture	result
Jan-1	A	lamp	5
Jan-1	A	lamp	4
Jan-1	B	table	3
Mar-6	A	lamp	9
Mar-6	A	lamp	7
Mar-9	B	table	7
Mar-9	B	table	6

I am plotting on a line chart fixture latest averages of min values by date.  So, here on Jan-1 the MIN(A) is 4 and Mar-6 MIN(A) is 7. Since the latest two results across the lamp happen to correspond to 4 and 7, I would like to display the average 5.5 for this dimension.  It is average across the latest 4 samples in my data set, I've shown only a small portion of the data.  I do not have date as a dimension because I am showing this summary per fixture in a line chart.

I tried to keep fixture as a dimension and then to use avg(aggr(min(result), date, id)) but avg iterates over the entire data set instead over the last 4 records.  In the table chart I played with the accumulation but with no success, probably because I don't have a date as a DIM. Same issue when I try to use rangeavg(below()). Can someone lend a hand, maybe some set analysis to restrict my average to the top 4 dates?

Thank you.

[Oleg_Troyansky]

Try something like this (writing from memory, so verify the syntax):

avg(

     aggr(

          rangemin(

               above(min(result), 4)

          )

     , date, id)

)

The Above() function with 4 should give you the last 4 entries, and the rangemin should get the minimum of the 4, and the rest is similar to your own calculation.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

[sunny_talwar]

May be this expression:

=FirstSortedValue(Aggr(If(date*10000 + result >= Max(Total <fixture> date*10000 + result, 4), result), fixture, date, result), Aggr(date*10000 + result, fixture, date, result))

[Anonymous]

Thanks Oleg!

You have a great memory, the syntax was right on.

However, I was not able to quite get the result I was after.

I'm attaching a sample file, I would appreciate it if you can take a peek?

I've attached both a sample app and data for your consideration.

Once I remove the date from my dimension, things start to fall apart (& it kind of makes sense).

Regards

[sunny_talwar · Accepted solution]

May be this:

FirstSortedValue(DISTINCT Aggr(avg(aggr(NODISTINCT rangeavg(below(min(price), 0, 4) ), date)), date, rep, product, level), -Aggr(date, date, rep, product, level))

[Anonymous]

Happy Holidays Sunny & Oleg!

Works like a charm Sunny...

Sunny, I also liked your approach below but didnt try it. 

It seems to work around the QV functions but probably boat loads faster.