topic Re: rangeavg or set analysis in QlikView

rangeavg or set analysis

Anonymous — Mon, 19 Dec 2016 23:30:43 GMT

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.

Re: rangeavg or set analysis

Oleg_Troyansky — Tue, 20 Dec 2016 00:06:26 GMT

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!

Re: rangeavg or set analysis

sunny_talwar — Tue, 20 Dec 2016 03:38:53 GMT

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

Re: rangeavg or set analysis

Anonymous — Thu, 22 Dec 2016 00:18:41 GMT

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

Re: rangeavg or set analysis

sunny_talwar — Thu, 22 Dec 2016 02:01:52 GMT

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

Re: rangeavg or set analysis

Anonymous — Thu, 22 Dec 2016 15:43:20 GMT

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.