Categories
How do I do a countif or sumif like excel
Hello all,
I am new, and not sure if i am in the correct discussion.
I have a table with a column filled with name [example: John, Peter, Alice]
How do I get the total number of time John appeared in the column?
Is there something similar like "Counf If" or sumif.
Thank you.
Answers
-
I am new, and not sure if i am in the correct discussion.
Welcome, what product (the full name and version ideally) are you using or asking about? That's what defined if you are in the right place. If not, don't worry, somebody will move your question in the right place (so no need to post it again somewhere else).
0 -
hihi..
I think the product is using Oracle Business Intelligence Enterprise Edition 11.1.1.7 (OBI).
Thank you.
0 -
Ok, so I will skip all the usual speech about how old and unsupported that version is (but feel free to tell to whoever is managing that internally that an upgrade would be welcome!).
If you have a column with names, by default you will see the duplicate entries because without any aggregation rule all the rows are visible.
To get a counting you can add a new column with a formula like: SUM(1)
This should force an aggregation, and your rows, when identical, will be grouped into a single row with the counting that will increment.
There can be various reasons of why this doesn't work, and it depends on everything that makes OBIEE what OBIEE is and that I'm skipping here (because that would maybe be too much for now).
Other ways to do it would be using a formula like: COUNT("table name"."column name with names")
0 -
"There can be various reasons of why this doesn't work, and it depends on everything that makes OBIEE what OBIEE is and that I'm skipping here (because that would maybe be too much for now)."
It's Friday. Yes it's too much
0 -
haha! it really don't work 😅
Guess no choice but if anyone had a workaround, please share with me.
0 -
SUM(case when "Products"."Brand" = 'BizTech' then 1 else 0 end)
0 -
How can it not work? What did you do to debug the thing?
It does work out of the box in OBIEE. If it doesn't work it's because you are asking this but try to do something else (XY Problem) or your environment is totally weird and wrong to the point of breaking basic features like auto aggregation of attribute columns.
In both cases, only you can find and fix your issues because they depends from your content (models), and OBIEE is empty when installed: the model is yours, only you have that model and only you can observe how it behave and act accordingly.
Ask to somebody who developed that thing how to do what you need to do.
0 -
Yeah this will work perfectly on just about any version that I remember going back to 7.0.
My formula is a much more inconvenient way of doinjg it
0 -
Hi all,
I got it working, one of my column got error that why it wasn't working initially.
Thank you so much for the help!😃
0
