topic Divide Row 2 By Row 1 in a Straight Table in QlikView

Divide Row 2 By Row 1 in a Straight Table

Fri, 29 Apr 2011 20:20:40 GMT

I have a list which contains regions and values. I would like to divide a value for each region by the value in region 1 (e.g., "US"). The only result I get is 100% for region 1 ("US") divided by region 1. All the other rows are null.

Region Data % of US

US 20 100%

EU 50

JP 10

Here is an example of the code I am using ...

(Sum (Data)) / (Sum ({$<region={'US'}>}Data))

Any help you can offer would be greatly appreciated. Thank you in advance.

Divide Row 2 By Row 1 in a Straight Table

patrickanderson — Fri, 29 Apr 2011 21:05:07 GMT

This is the same question I asked above. So I am following.

Divide Row 2 By Row 1 in a Straight Table

johnw — Fri, 29 Apr 2011 21:22:06 GMT

Maybe this?

sum(Data)/sum(total {<Region={'US'}>} Data)

I think the problem is that each row is taking the intersection of your set and that row's dimension values. For anything but US, that intersection is null, so the whole expression returns null. By using "total", you tell it to ignore the dimension values when calculating the number on the bottom. If you have other dimensions, though, it might be a little more complicated. Let's say you have a "Data Type" field in the chart. You might need this:

sum(Data)/sum(total <"Data Type"> {<Region={'US'}>} Data)

Divide Row 2 By Row 1 in a Straight Table

Fri, 29 Apr 2011 21:35:09 GMT

That worked perfectly! Thanks!