Ignore related values

[hfkchristian]

Hi all

In my qlikview-application I have some fields which are related to each other. Like, when I select a value in field A, there are only some possible values in field B and C.

But in one of my charts I need to calculate a sum for ALL values in field C, not just the possible ones. Field A and B are dimensions in the chart, and as expression I calculate the sum of another field D.

The problem is that as long as field A is a dimension, the expression i automatically limited to the possible values of field B and field C. Instead, I want to calculate the sum of D for all values of field C, but still limited to the possible values of field B.

I tried this:

SUM({$} total<A, B> D)

but it doesn't work.

This does not work either:

SUM ({$ <A=, B=P(B), C=>} D)

How can I do this?

[cesaraccardi]

The set analysis in the AGGR() function is incorrect syntax-wise, you have to do that on the sum() function. The fact that the result doesn't change is down to the set analysis expression not being correct as swuehl mentioned, in the example A is a field name:

=concat(aggr(num(sum({$<A=>} D),'# ##0'),C),chr(13))

[hfkchristian]

My mistake, it should be A=. But still the expression works in your sample, but not in my real application. Maybe it has to do with the way I load data? I use this script:

LOAD field1 AS A,
trim(subfield(upper(field2),',')) AS B,
subfield (field3,',')) AS C
FROM
$(VDirectoryExcel)excel.xls
(biff, embedded labels, table is Sheet1$);

[swuehl]

Looks fine on first glance.

Could you upload a small sample QVW?

You can use some fake data, but the structure should be comparable to your original.

[hfkchristian]

I don't think I can upload it. We use multiple QVD files, QVW files and Excel files. It will be too complex.

What we do in the end is removing field C from the first table. So we loose one of the chart expressions, but at least we can calculate the other one, and that is more important. The solution will be to manualy select values from field C when needed. Thank you for trying to help