topic Re: Ignore related values in QlikView

Ignore related values

hfkchristian — Fri, 15 May 2015 09:03:48 GMT

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?

Re: Ignore related values

swuehl — Fri, 15 May 2015 09:08:12 GMT

What about

SUM( {1} TOTAL D)

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

Re: Ignore related values

swuehl — Fri, 15 May 2015 09:09:35 GMT

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)

Re: Ignore related values

hfkchristian — Fri, 15 May 2015 10:18:43 GMT

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

Re: Ignore related values

cesaraccardi — Fri, 15 May 2015 11:00:48 GMT

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

Re: Ignore related values

hfkchristian — Fri, 15 May 2015 11:09:59 GMT

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

Re: Ignore related values

cesaraccardi — Fri, 15 May 2015 11:35:43 GMT

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.

Re: Ignore related values

hfkchristian — Thu, 21 May 2015 08:30:22 GMT

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.

Re: Ignore related values

hfkchristian — Thu, 21 May 2015 09:22:14 GMT

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.

Re: Ignore related values

swuehl — Thu, 21 May 2015 09:24:47 GMT

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

Re: Ignore related values

cesaraccardi — Thu, 21 May 2015 10:35:46 GMT

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))

Re: Ignore related values

hfkchristian — Fri, 22 May 2015 07:48:41 GMT

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$);

Re: Ignore related values

swuehl — Fri, 22 May 2015 10:38:54 GMT

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.

Re: Ignore related values

hfkchristian — Tue, 02 Jun 2015 08:00:59 GMT

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