Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
limingthefirst
Contributor III
Contributor III

Sum intersection

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

111.png

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

18 Replies
sunny_talwar

May be ignore selection in COUNTRY field?

=Sum({<COUNTRY>}QTY)


Capture.PNG

limingthefirst
Contributor III
Contributor III
Author

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.

sunny_talwar

Then may be try this

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


Capture.PNG

limingthefirst
Contributor III
Contributor III
Author

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?

sunny_talwar

May be this

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

limingthefirst
Contributor III
Contributor III
Author

Thank Sunny,

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

sunny_talwar

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

tresesco
MVP
MVP

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)

sunny_talwar

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