topic Value from specified row in chart table in QlikView

Value from specified row in chart table

lawrenceiow — Fri, 15 May 2015 13:57:03 GMT

I have a need to retrieve the value from a specified row of a column in a chart and use it in every row.

As an example:

Date	Qty	Average	Special	*=fabs(Average(RowNo()=3))**
01/05/2015	10	10	140	0
02/05/2015	20	15	210	0
03/05/2015	12	14	196	196
04/05/2015	9	12.75	178.5	0
05/05/2015	18	13.8	193.2	0
06/05/2015	21	15	210	0

So, the special column multiplies the figure from the average column with the value in the 3rd row of the average column. If I create an expression =fabs(Average*(RowNo()=3)) I get the number I want but only on the 3rd row. How do I write the expression to get the values as shown in the Special column?

Re: Value from specified row in chart table

petter — Fri, 15 May 2015 13:59:50 GMT

You can use:

Top(TOTAL [Average], 3 )

Re: Value from specified row in chart table

sunny_talwar — Fri, 15 May 2015 14:08:55 GMT

This is awesome Petter‌. Thanks for introducing me to another function in QlikView.

Best,

Sunny

Re: Value from specified row in chart table

lawrenceiow — Fri, 15 May 2015 14:11:12 GMT

Excellent, thanks @Petter_Skjolden. Such a quick answer too.

Re: Value from specified row in chart table

petter — Fri, 15 May 2015 14:11:51 GMT

That's exactly what I like about QlikView - being able to discover new useful techniques after years of using it

Re: Value from specified row in chart table

sunny_talwar — Fri, 15 May 2015 14:15:49 GMT

I second that.

Although I am still a new to QlikView, but I have learnt a whole lot by just being active on the community. I learn less by answering to the things I know, but more from looking at other peoples responses which gives me new ideas.