topic Re: Sum intersection in QlikView

Sum intersection

limingthefirst — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I have a trouble to sum intersection and union value.

For example:

Red color is attributes for S1, then I need to sum each A and C value,

sum(A) should be 6

sum(C) should be 24

When I select null of F4, I need each value of sum(F1)

This logic is in one sheet of report, and 2 hundred million data in fact qvd.

I've tried to group by this in script, but it is too slow and has linkage issue in report when I select other dimensions

The value maybe calculated in text object and bar chart and so on.

Can anybody give me some suggestions to me?

Thanks all.

The requirement is changed...

Add sample here

Re: Sum intersection

Anil_Babu_Samineni — Mon, 18 Dec 2017 06:47:01 GMT

May be use Aggregation like When should the Aggr() function NOT be used?

Re: Sum intersection

limingthefirst — Mon, 18 Dec 2017 06:59:47 GMT

Hi Anil,

Thanks for your link,

I've tried it =SUM(TOTAL <F1> F3)

but it is not what I want when select S1:

Re: Sum intersection

Anil_Babu_Samineni — Mon, 18 Dec 2017 07:03:50 GMT

You mean, You want to compare row level like S1 having C which has common to all. So, You need to pick 7+8+9 ??

Re: Sum intersection

limingthefirst — Mon, 18 Dec 2017 07:20:45 GMT

Yes, sum(C)=7+8+9, sum(A)=1+2+3

Re: Sum intersection

Anil_Babu_Samineni — Mon, 18 Dec 2017 12:00:10 GMT

Can you attach your sample?

Re: Sum intersection

tresB — Mon, 18 Dec 2017 12:25:31 GMT

May be this:

=SUM(TOTAL <F1> {1} F3)* Avg(1)

Re: Sum intersection

limingthefirst — Mon, 18 Dec 2017 14:34:42 GMT

I like your answer, It's working, but there is an issue is that when I select S1 and the total result is 45 not 30.

Re: Sum intersection

limingthefirst — Mon, 18 Dec 2017 14:36:53 GMT

Hi Anil, I've upload demo.qvw for you, and the requirement is changed, the comment is added in qvw file. thanks.

Re: Sum intersection

Anil_Babu_Samineni — Mon, 18 Dec 2017 14:49:52 GMT

May be this?

=SUM({$<COUNTRY>}QTY)

=SUM({1} QTY)

Re: Sum intersection

sunny_talwar — Mon, 18 Dec 2017 14:50:21 GMT

May be ignore selection in COUNTRY field?

=Sum({<COUNTRY>}QTY)

Re: Sum intersection

limingthefirst — Thu, 21 Dec 2017 08:01:55 GMT

Hi Sunny,

Not simply ignore selection in COUNTRY field. Sorry that I didn't give requirement clearly.

When select CHINA, we can get VENDOR 1/2/3, not have VENDOR 4, so if ignore selection in COUNTRY field, the VENDOR 4 has appeared. I need to calculate VENDOR 1/2/3 's total sum value.

Re: Sum intersection

sunny_talwar — Thu, 21 Dec 2017 12:22:25 GMT

Then may be try this

=Sum({<COUNTRY, VENDOR = p(VENDOR)>}QTY)

Re: Sum intersection

limingthefirst — Mon, 25 Dec 2017 08:35:55 GMT

Hi Sunny,

I've check some days, it work in some objects, thanks very much.

And now I add a Chart:

To show which VENDOR has more than 1 COUNTRYs

=count({<VENDOR=p(VENDOR),VENDOR={"=count(distinct COUNTRY)>1"}>}distinct VENDOR)

When select ENGLAND and ITALY, it show 2 VENDORs, I want to show 3, because both ENGLAND and ITALY has 3 VENDORs, not to count intersection.

When select one COUNTRY such as CHINA, it should show one bar and value = 3

Could you help me to correct it?

Re: Sum intersection

sunny_talwar — Mon, 25 Dec 2017 17:21:19 GMT

May be this

=Count({<VENDOR=p(VENDOR),VENDOR={"=Count(DISTINCT {<COUNTRY>} COUNTRY)>1"}>}DISTINCT VENDOR)

Re: Sum intersection

limingthefirst — Tue, 26 Dec 2017 09:49:22 GMT

Thank Sunny,

It working,and I find that different order of these conditions in expression give different result. Why?

Re: Sum intersection

sunny_talwar — Tue, 26 Dec 2017 09:52:56 GMT

Not sure what you are trying to say? Different order of these conditions in expression give different result?

Re: Sum intersection

tresB — Tue, 26 Dec 2017 10:01:51 GMT

Hi Sunny,

Here the first field modifier (vendor) might not be necessary since the later one would finally be considered. Right?

=Count({<VENDOR=p(VENDOR),VENDOR={"=Count(DISTINCT {<COUNTRY>} COUNTRY)>1"}>}DISTINCT VENDOR)

Re: Sum intersection

sunny_talwar — Tue, 26 Dec 2017 12:08:05 GMT

Yes, you are absolutely right. I didn't see it and that is why forgot to mention it and remove. Thanks for correcting it