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
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
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
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.
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
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
try this.
=Sum(Count([Mesh Group];All) In ([Mesh Group]))
No luck with this formula Amit. it gibes me a constant number of 89 top to bottom.
Can you share the screenshot.
Or try to add other dimensions in the IN context and see
Hi,
Formula provided by Sushil Pandya.
=Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area])
Attached are the sreenshots
Thanks,
Jothi
Thanks Jyoti ! For emphasizing on Sushil's solution. It made me try hard to find the defect in my formula.
Appreciate it!!
Thanks
GD
Hi Gaurav,
Glad it worked!!.
Thanks,
Jothi