How to output the value of another alternate state

[uacg0009]

Hi all,

I have some data and two lists like below:

one of list's alternate is inherited, another one is GROUP1 that I made.

now what I want is:

if I choose A from ITEM2 and then choose B from ITEM1, the result what I want to show is:

Because only VIP=1 and VIP=3 have bought both A and B, so the number is 2.

How to do it?

Thank you all!

[Anonymous]

Changjun

I reckon from you data that not only VIP=1 and VIP=3, but also VIP=2, so reckon the number should be 3.

Is there something I am misunderstanding ?

Best Regards,     Bill

[#3]

Changjun,

Did you try the basic :

count({<Group1 * Group2>} VIP) ?

Fabrice

[tresesco]

Try like:

=Count({<VIP=P({<ITEM=[GROUP1]::ITEM>})>}VIP)

[jerem1234]

See if attached is what you're looking for. The formula I wound up using was:

=count({$<VIP = p({$}VIP)>*<VIP = p({GROUP1}VIP)>}distinct VIP)

Hope this helps!

[uacg0009]

Hi Markham,

Thank you for your attention, because VIP=2 didn't buy ITEM A, so when I choose A from ITEM2 and B from ITEM1,

it didn't include VIP=2, so the number is 2.

[uacg0009]

Thank you Fabrice, but do you have any idea to output the column of ITEM2?

because I can't output the value of GROUP1.

[uacg0009]

Thank you tresesco, but could you have any idea to output the column of ITEM2?

because I can't output the value of GROUP1.

[uacg0009]

Thank you jerem, it is very useful for me.

But I also have a problem, if I don't want to concat the ITEM, do you have any idea to use other function to output the

column of ITEM?

Thank you very much.

[tresesco · Accepted solution]

You can use only() instead of concat in that case. PFA  (i have just tweaked jerem1234 's example)