topic Re: Compare Value from last row value in QlikView

Compare Value from last row value

Wed, 25 Nov 2020 16:16:04 GMT

rowid value arrow

1 1001

2 999

3 504

- 1000

I need to show up and down arrow by comparing the value of blank row id with first row, second row and so on.....

I tried to implement the below calculation but not able to achieve it

if( sum([Value]) <= SUM({$-<rowid = {'?*'}>}[Value]),'qmem://<bundled>/BuiltIn/arrow_n_g.png','qmem://<bundled>/BuiltIn/arrow_s_r.png')

Re: Compare Value from last row value

swuehl — Mon, 16 May 2016 09:08:25 GMT

Not sure about the (chart object) context you are trying to use this expression.

Maybe something like this (assuming '-' indicates a NULL in the rowid record)

Create a variable vThreshold like

=Sum({1<value = e({<rowid = {"*"}>}) >} value)

or (if it's in fact a '-' chararacter)

=Sum({1<rowid -= {'-'}>} value)

Do you get the correct value?

Then just use the variable in your expression:

if( sum([Value]) <= vThreshold

,'qmem://<bundled>/BuiltIn/arrow_n_g.png','qmem://<bundled>/BuiltIn/arrow_s_r.png')

Re: Compare Value from last row value

Kushal_Chawda — Mon, 16 May 2016 09:15:41 GMT

Create the Variable on front end, vBlankRowId and put below expression

SUM({1-$<rowid = {'*'}>} Value)

then write expression

if( sum([Value]) <= $(vBlankRowId)

,'qmem://<bundled>/BuiltIn/arrow_n_g.png','qmem://<bundled>/BuiltIn/arrow_s_r.png')

Re: Compare Value from last row value

Mon, 16 May 2016 09:30:36 GMT

It's not working, increasing number of rows and adding blank rows.

Re: Compare Value from last row value

sunny_talwar — Mon, 16 May 2016 09:45:07 GMT

I think you will might benefit using a technique similar to As-Of Table:

The As-Of Table‌

Alternatively, you might need to use Above/Before to achieve what you are looking for:

Missing Manual - Above() and Below()

Re: Compare Value from last row value

ankit777 — Mon, 16 May 2016 10:07:32 GMT

You need to create a variable like this

=sum(if(isnull(rowid),value,0))

Now use this in your expression:

if you name the variable as vColor

if( sum([Value]) <= $(vColor),'qmem://<bundled>/BuiltIn/arrow_n_g.png','qmem://<bundled>/BuiltIn/arrow_s_r.png')

Re: Compare Value from last row value

Mon, 16 May 2016 10:32:05 GMT

This does not compare the blank rowid with other values

Re: Compare Value from last row value

Mon, 16 May 2016 11:51:56 GMT

Can it be achieved by set, as need to only compare blank row id with the one's having value?

Re: Compare Value from last row value

sunny_talwar — Mon, 16 May 2016 12:23:39 GMT

I am not entirely sure what you are trying to do, but may be try this:

If(Sum([Value]) <= Sum(TOTAL {$-<rowid = {'?*'}>}[Value]), 'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')

Re: Compare Value from last row value

Mon, 16 May 2016 13:05:19 GMT

Thanks, it worked!!!