Multiple dimension values in two cell. How to count properly?

[#1]

Hi there,

I have a dataset like the table below (larger though):

ID	App	Business function	Name
231	BroadDisplay	Kok; Bok; Tok	Puk; Put
245	SmallDisplay	Stone; Bone; Home	Stuck; Cut; Duck

Now I made subfields for 'Business function' and 'Name' like this:

                    SubField("Business function", ';') as Tags,

                    SubField("Name", ';') as Tags2,

The following problem now occurs when I want to count the Business function or Name:

ID	App	Tags (Business function)	Tags2 (Name)
231	BroadDisplay	Kok	Puk
231	BroadDisplay	Bok	Puk
231	BroadDisplay	Tok	Puk
231	BroadDisplay	Kok	Put
231	BroadDisplay	Bok	Put
231	BroadDisplay	Tok	Put
245	SmallDisplay	Stone	Stuck
245	SmallDisplay	Bone	Stuck
245	SmallDisplay	Home	Stuck
245	SmallDisplay	Stone	Cut
245	SmallDisplay	Bone	Cut
245	SmallDisplay	Home	Cut
245	SmallDisplay	Stone	Duck
245	SmallDisplay	Bone	Duck
245	SmallDisplay	Home	Duck

This would mean that even with the set analysis below my business function Tok, Kok, Bok is still counted twice. And Stone, Bone, Home is counted tree times if I am correct:

count(aggr(sum(DISTINCT [Tags]), [ID]))

OR

count(aggr(sum(DISTINCT [Tags]), [Tags2]))

I somehow want to combine the DISTINCT ID and Tags2 but I don't know if that is possible. Does anyone have a clue what I am talking about and would show me the solution (A)?

Would be great help if anyone knows how!!!

Tnx

[#2]

can you please send the screenshot of what you want the end result look like?

[sunny_talwar]

May be this:

Sum(Aggr(Count(DISTINCT Tags), Tags2, ID))

[#4]

First of all tnx for the quick replies . I want the following but I just want this to happen in the set analysis:

• count(aggr(sum(DISTINCT [Tags]), [ID]))

ID	App	Tags (Business function)	Tags2 (Name)
231	BroadDisplay	Kok	Puk; Put
231	BroadDisplay	Bok	Puk; Put
231	BroadDisplay	Tok	Puk; Put
245	SmallDisplay	Stone	Stuck; Cut; Duck
245	SmallDisplay	Bone	Stuck; Cut; Duck
245	SmallDisplay	Home	Stuck; Cut; Duck

Sunny T I will try yours in a sec, it might be the one I am looking for.