Counting Unique Variables in Webi (Related to duplicates)

[Former Member]

Hello,

I'm not sure where to post this, so I hope this is the correct section. I'm trying to count the number of unique forms and I've tried using both the count and sum functions using distinct and all, but nothing is giving me the desired results.

First Name	Last Name	Form Number
John	Smith	123
Annie	Jones	139
Duncan	Hines	139
Betty	Crocker	180

I'm working on a summary table that should look like the one below.

Number   of Unique Forms	3
Number of Combined Forms	1

Any suggestions?

By the way I'm using an older version of BOXi, if that matters. We're still on 3.1 here.

[Former Member]

Hi,

Try the below solution,

For Gettting,

Number of Unique Forms = Count([Form Number];Distinct) = 3

Number of Combined Forms = Sum(If(Count([Form Number];All)In([Form Number])>1;1;0)) = 1

Thanks

Subasree

[Former Member]

That would give 2 for the combined forms because two rows meet that criteria.

[Former Member]

Based on the data that you've provided, I've created a table with two rows in it as you have.

To get the three, I have used =Count([FormNumber])

To get the one, I have used =Count([FormNumber]) Where (Count([FormNumber];All) In ([FormNumber])>1)

Regards,

Mark

[Former Member]

Hi,

Mark,

My example was flawed because my forms are alphanumeric. So instead of 123, 139 or 180 it would be like I-123, E-139 and S-180.

The above suggestions didn't give me the correct results either. For some reason when I put in the Count[Form Number] function I get 2 in BOXi.

[jyothirmayee_s]

Hi,,

Use Distinctcount() instead of Count()

Thanks,

Jothi

[Former Member]

Hi Jothi,

Thanks for the suggestion, but distinctcount gives me the following error {Invalid identifier 'Distinctcount' at position 1. (WIS 10022)}.

[Former Member]

It worked fine for me Jonathan. I used alphanumerics in the FormNumber field so I'm not sure what's different.
I have no idea why you get 2. I've taken your four rows, typed them into notepad and used it as a data source in Webi Rich Client. I had a vertical table with all the rows in and then a horizontal table with just the two sets of words and the two counts that you were seeking, using the formula that I had. It worked fine (obviously, or I wouldn't have posted it!) so I'm not sure what you're doing differently. Are there any filters on your blocks?

[Former Member]

Is that a new function?