Oracle Communities

Oracle Analytics and AI

Child Item

Adding a summary for specific value

Hello. I just created a new result tabel with average sales and I need to get a summary that show me how many value is under specific number (lets's say under 20)

Like below. I need the summary value.

Is it posible in Oracle 11g ?

Week nr	regio 1	regio 2
Week 1	10	20
Week 2	20	20
summary	1	2

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Hi,

create an analysis with your Week 1 / Week 2, then turn it into a union query which uses a sum(case when Measure < 20 then 1 else 0 end) formula for the equivalent numeric column.

Use tricks of formatting etc to make it look like a 'normal' total column, job done.

3280272

OK but I need to keep a value like 20, 10 etc in tabel body and get only a summary for how many value under 20 i have.

Is this solution (that you give) give me that result?

And the noob question - how to change a view on a union query ? Did you mean a edition of the formula? Or change a view in result tabel mode?

P.S.

The tabel I show in example is only a "working sample" - the final tabel will look a lot better

Robert Angel

Yes - it will give you that solution - though you will need to substitute the full syntax for your measure in the sum(case... that I passed to you.

In the data pane of answers there is a control near the subject area which allows you to add another subject area (or the same subject area!) by clicking on it, adding the subject area and then adding the equivalent (as in same datatype) columns as the original analysis - it is this that I am recommending.

The view (table, pivot, graph, whatever) that you use on the union'ed data set is created in the normal manner once you have created the data model.

Robert Angel

Pictures painting a thousand words; -

Obviously in your case the columns you are selecting are not as above, just trying to give you the basics of where to find union / union all etc and how to use them...

Robert Angel

Final tips to a 'noob' - we are all noobs at something / sometime after all...

Do give yourself a meaningful display name, old hands tend to ignore those left on number formats.

Do give points if others help you (I am not seeking points here!!!) and mark your questions with helpful / answer - or assumed answered if you got there useful after 'partial advice'.

Do give full detail of exactly what you are trying to do and what you have tried - no criticism intended or implied here!

Do do a basic google / forum search first - you can save yourself a lot of time.

Finally, do be more specific than 11g on versions - de-support might be your bigger problem!!

3280272

Such an exhausting response

This should solve my problem.

Thank you.

And thx for a good tips!

Robert Angel

Thank you is like gold here too!!

You are most welcome!