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?

[swuehl]

What about

SUM( {1} TOTAL D)

{1} will ignore any selection you've made and TOTAL qualifier the dimension(s).

[swuehl]

Hm, I may misunderstand what you are trying to achieve. Could you upload a small sample or some lines of sample data?

edit: Maybe

SUM( Total<B> D)

?

[hfkchristian]

Here is some example data.

I have a table which defines how the fields are related. Columns B and C are subfields, loaded to QlikView with the subfield function.

A	B	C
A1	B1, B2	C2,C3
A2	B3	C1,C2,C2
A3	B2,B3	C1,C2

And there is a table with the values of field D

B	C	D
B1	C2	100
B1	C1	200
B2	C2	150
B2	C1	50
B1	C3	300
B3	C2	500

Then in my chart, A and B are dimensions. The result for SUM(D) will be,assuming only the value A1 is selected:

A	B	SUM(D)
A1	B1	100+300
A1	B2	150

The sum(D) values for C1 does not appear in the table, because C1 is not related to A1. Instead, I want this

A	B	SUM(D)
A1	B1	100+200+300
A1	B2	150+50

[cesaraccardi]

Hi Christian,

What is the current key to link the first table and the table that contains the field D? If that includes C, can't you just remove it and use field B only?

Kind Regards,

Cesar

[hfkchristian · Accepted solution]

The problem is that most of the times I need field C too. It's just in one expression I want to ignore it.

What I really want is to show both in the same chart:

A	B	SUM(D) unmodified	SUM(D) with field C ignored
A1	B1	100+300	100+200+300
A1	B2	150	150+50

[cesaraccardi]

I have built a sample application using the AGGR() function to manipulate the aggregation of field D. Please have a look and let us know if this is what you are looking for.

[hfkchristian]

Thanks. Your sample is exactly what I like to do. Unfortunately I can't get it to work when I try that expression on my real data. Not sure why.

[hfkchristian]

I tried to print the values of the temporary AGGR table in a textbox to see how it works. I didt it like this:

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

When nothing is selected I get all the values grouped by C as expected. But as soon as I select  A1 or A2 I dont't get all the values anymore. But only the related values. Nothing changes if I move the set expression to the SUM function instead of the AGGR function.

[swuehl]

Are A1 and A2 field names or field values? In your expression, they should be field names.