Count of Duplicates across two or more columns

building reports webintelligence
1

Hi!

Forum newbie here. I’ve tried searching for help with my query and though I’ve found a couple of helpful bits, it’s not quite giving me what I need.

I am trying to do the equivalent of a Countifs in Webi 4, but I’m not getting the values I expect. I am trying to show a count of duplicate rows, where a PO number and a Comment are matching, for example, this is what I want to show:


PO Number   Invoice Number   Comment   Count
12345	     4567a	        AAAA1	   3
12345	     Adf47	        AAAA2	   1
12345	     Er45	         AAAA1	   3
11145	     4567a	        AAAA3	   2
11145	     45dsa4fsa	    AAAA3	   2
12345	     56wer	        AAAA1	   3

So as you can see, I want to count where both the PO number and the Comment appear together in different rows, and show the number of times in the count column.

I’ve been using this formula: =Sum(Count([PO Number];All) In ([PO Number];[Comments])) however I am getting the count of 4 for PO 12345, so it’s like it’s disregarding the Comments column.

Sorry for that formatting, I can’t upload files! Hope it comes out OK.

Appreciate any help you can give :)[/code]

MojoJojo (BOB member since 2019-08-19)

2

Hi,

Here is how I would do it.

  1. Make a variable that concatenates the 3 objects into 1 string.
  2. Place 3 objects each in a column. Place this formula in the 4th cell: =Count([VariableName])

It is possible that BO will only count per row. Then you need to add the ForAll() function.

ndam (BOB member since 2016-11-09)

3

Thanks ndam!

I only need to concatenate the two objects (PO Number and Comment) I think, but will certainly give that a go.

If anyone else can advise on what’s wrong with the formula I’ve been using, I’d appreciate the insight, just so I know where I’ve gone wrong! :smiley:

I’m not sure if it makes a difference, but the “Comment” field is brought in from another universe, via the merged dimension and new detail variable method. Could that be causing the formula to not work as expected?

I have this morning tried the formula on a different query where all the data items are from the same universe, and it worked as expected, so I’m wondering if it’s the above.

Good workaround though, thank you!

MojoJojo (BOB member since 2019-08-19)