Sum intersection

[limingthefirst]

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

[sunny_talwar]

May be ignore selection in COUNTRY field?

=Sum({<COUNTRY>}QTY)

[limingthefirst]

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)

[limingthefirst]

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 · Accepted solution]

May be this

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

[limingthefirst]

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]

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