topic Re: Use value from another row in calculation in QlikView

Use value from another row in calculation

Tue, 13 Nov 2012 18:02:21 GMT

I have a straight table with two dimensions, Line Number and Line Description, the expresssion sums Balance.

I want to create a second expression that uses the Balance sum from the row where Line Description = 'Net Sales' to calculate a percent value. So, on the row where Line Description = Product Sales it would look something like SUM(Balance) / [SUM(Balance) from Net Sales]. I can't use Above or Below as the number of rows is not static.

Any ideas? Thanks in advance for any help.

Re: Use value from another row in calculation

diegofcaivano — Tue, 13 Nov 2012 18:08:08 GMT

Hi. You may use Set Analysis.

SUM(Balance) / [SUM({<[Line Description]={'Net Sales'}>} Balance)]

Regards.

Re: Use value from another row in calculation

Tue, 13 Nov 2012 18:15:07 GMT

Appreciate the quick reply! This doesn't work as it calculates the second part as 0 except where the dimension matches the Set Analysis. So, if I only do the [SUM({<[Line Description]={'Net Sales'}>} Balance)] part all values come up as zero except the row where the Line Desc is actual Net Sales. I am assuming Set Analysis would still honor the dimensions so it cannot do this calculation. Thanks though.

Re: Use value from another row in calculation

Jason_Michaelides — Tue, 13 Nov 2012 20:38:42 GMT

Try using the TOTAL qualifier along with the set analysis:

SUM(Balance) / [SUM({<[Line Description]={'Net Sales'}>} TOTAL Balance)]

That should disregard the dimensions.

Hope this helps,

Jason

Re: Use value from another row in calculation

Tue, 13 Nov 2012 20:48:47 GMT

Jason,

You are my favorite person today! Thanks so much!

Kindest regards,

Heather

Re: Use value from another row in calculation

Jason_Michaelides — Tue, 13 Nov 2012 21:10:40 GMT

You're very welcome 🙂