Problem calculating measure in table

[Anonymous]

Hi,

I am very new to Qlik and am doing some basic calculations and visualisations and trying to learn its limitations. I hope the below makes sense!

I am trying to understand why I am only getting a volume impact figure in the row with a Buyer Cost of 1. In the table I have built.

Buyer cost is set as my dimension and all the others set as measures

Buyer Cost	% Of Total Quotes Requested	% Of Total Orders Taken	Volume Impact
0.8	3.20%	4.40%	-
0.85	3.70%	4.00%	-
0.9	4%	3.60%	-
0.95	10%	11%	-
1	58%	60%	0.00%
1.05	12.50%	11%	-
1.1	3.10%	3%	-
1.15	2.50%	2%	-
1.2	3%	1%	-

he set analysis I am using is as follows

1-(((sum([OT]) / sum(Total[OT])) / (sum([OQ]) / sum(Total[OQ]))) /

(((((sum({<[Buyer Cost]={1}>} [OT]))) / ((sum(Total[OT]))))) / (((sum({<[Supplier Cost]={1}>} [OQ]))) / ((sum(Total[OQ]))))))

My thinking is that it is not returning a value for the other rows as the Buyer Cost of 1 is not in this data. I was hoping the formula would calculate the bottom half of it independently but it appears this is not the case.

I can calculate this easily in excel but my thinking was that in Qlik Sense would be easier to switch the data between different buyers using filters. We have a lot of buyers and In Excel we have to keep reloading data for each one

Hope this makes sense and any help you can give would be greatly appreciated.

[sunny_talwar · Accepted solution]

May be you need this

1-(((sum([OT]) / sum(Total[OT])) / (sum([OQ]) / sum(Total[OQ]))) /

(((((sum(TOTAL {<[Buyer Cost]={1}>} [OT]))) / ((sum(Total[OT]))))) / (((sum(TOTAL {<[Supplier Cost]={1}>} [OQ]))) / ((sum(Total[OQ]))))))

Or this

1-(((sum([OT]) / sum(Total[OT])) / (sum([OQ]) / sum(Total[OQ]))) /

(((((sum(TOTAL {<[Buyer Cost]={1}>} [OT]))) / ((sum(Total[OT]))))) / (((sum({<[Supplier Cost]={1}>} [OQ]))) / ((sum(Total[OQ]))))))

[Anonymous]

Thank you Sunny! Knew it would be something simple and obvious.