Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use value from another row in calculation

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.

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

View solution in original post

5 Replies
diegofcaivano
Partner - Creator
Partner - Creator

Hi. You may use Set Analysis.

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

Regards.

Not applicable
Author

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.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Jason,

You are my favorite person today!  Thanks so much!

Kindest regards,

Heather

Jason_Michaelides
Luminary Alumni
Luminary Alumni

You're very welcome 🙂