Count of duplicate values in a column - Web Intelligence

[former_member297523]

Hello All,

I have a Webi report with multiple columns, I'm trying to get a count of all the unique rows in column "Mesh Group" (as displayed in attachment 2). Basically, the column "Number of mesh group" which is right next to "Mesh Group" needs to display the count of all unique/duplicate values. For ex: "KM2" appears 2 times within the report, hence the "Number of mesh group" value must show 2 next to "KM2".

I have tried the count, count all & distinct functions so far, with no success. Can someone please help create a function that will achieve this for me ?

Thanks

GD

[Former Member · Accepted solution]

Hello Gaurav,

Please try with ForAll Context Operator.

Formula:

=Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area])

If you dont want to consider any of the Column then remove that from ForAll Parameter.

Regards,

Sushil Padhye

[former_member297523 · Accepted solution]

Thanks Sushil! What I was missing in your formula, is to add other dimensions that I did not specify in my question (since it's a very large table I'm working with). I later added all the dimensions under ForAll function and it worked like a charm.

Just wondering, if instead of adding each and every dimension used in the table, is there another function that will siolve the purpose, currently, if a new dimension is added to the table, the user has to be cognizant to add it to the variable formula as well.

Thanks again

GD

[former_member1064253]

Hi, For me it is =Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area]) not working.

this >> =Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area]) formaula is working and showing all duplicate count but not able to filter in report. Please suggest.

[jyothirmayee_s]

Hi Gaurav,

Glad it worked!!.

Thanks,

Jothi

[jyothirmayee_s]

Hi,

Formula provided by Sushil Pandya.

=Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area])

Attached are the sreenshots

Thanks,

Jothi

[former_member297523]

Thanks Jyoti ! For emphasizing on Sushil's solution. It made me try hard to find the defect in my formula.

Appreciate it!!

Thanks

GD

[amitrathi239]

try this.

=Sum(Count([Mesh Group];All) In ([Mesh Group]))

[former_member297523]

No luck with this formula Amit. it gibes me a constant number of 89 top to bottom.

[amitrathi239]

Can you share the screenshot.

Or try to add other dimensions in the IN context and see

[former_member297523]

thanks for your response Sushil.

I tried the formula you suggested, it only bring 1s under "number of mesh group" . It does not seem to pick the duplicate values sum.

Will Distinct function help ?

Thanks

GD

[Former Member]

If the data resides on the database, you need a GROUP BY select. Just an example, plain SQL (needs to be transfered into ABAP or CDS):

Data in column "data" of table "table": A1, A1, A2, A3, A3, A4

select count(*) as counter, data
from table
group by data

result: 2, A1; 1, A2, 2; A3, 1 A4

so A1 is there 2 times, A2 one time and so on.

[former_member297523]

thanks for the response Ronald.

I'm looking to create a variable at the report level, leveraging the available Webi functions in order to achieve this. Can you suggest a function formula that will help achieve this ?

Thanks

GD