Want to undertand difference between Aggregate BY through UI and Calculation.

Rajakumar Burra

Want to understand difference between Aggregate BY through UI and Calculation.

Example:

I want weekly Sum values against ID. It is working fine using “Aggregation By” ID through UI as per below image.

But I want to do this with My Calculations and don't want to use UI option Aggregation BY.

I tried it using AGGR BY , SUM BY. But no luck. Any help is appreciated.

Aggregate By.dva

Bret Grinslade - Oracle Analytics-Oracle

What does the calculation your are looking to do mean in general terms. For example if you have:

Week Date	ID	Planned	Week Number
7/28/2024	A	0	31
7/29/2024	A	1	31
7/30/2024	A	1	31
8/8/2024	A	1	32
8/9/2024	A	9	32

For Week 31 -- do you want 1 as the answer?
For Week 32 -- do you want 10?

Mostafa Morsy-Oracle

@Rajakumar Burra

I do not think the Values you think is correct is actually wrong please check the following

Screenshot 2024-08-08 at 1.08.56 PM.png

Also there are some NULL Values which not a problem as IFNULL Function should handle it

Rajakumar Burra

@Bret Grinslade - Oracle Analytics-Oracle Sorry I didn't explained correctly.

Below exactly what I need. My Table:

My Result expectation

Book1.xlsx

Rajakumar Burra

@Mostafa Morsy-Oracle Sum aggregations is always an issue.

Need different calculation or aggregation based on context.

I am able to get what i want with UI Aggregation method. Only issue is these options not available in Overalay charts.

Still I am not clear in concepts in calculations for below. If you have any examples or documentation is helpful.

• sum ( xyx by abc)
• AGGREGATE ( XYZ by abc)

SteveF-Oracle

Hi @Rajakumar Burra

I am not sure if you made progress, I had a moment to play around a little with your spreadsheet.

I tested sum with a FILTER Using, but I don't think that is scalable.

I think 'Aggregate By' should work for you, but in your case, you have 'Planned' and 'Executed' as text attributes. I went ahead and made them numbers (measures). You probably could convert yours to measures with a trick
SUM(0)+rest of calculation

AGGREGATE(measure BY dimension)
AGGREGATE(Planned BY Week)
AGGREGATE(Executed BY Week)

I put it in a Pivot table for demonstration only to easier see the week totals. I also left the original 'Planned' and 'Executed' for manual testing of totals. I am not sure how you want your layout (you mentioned Overlay) , I was merely attempting to test 'Aggregate At' and 'Aggregate By' in a calculation.

I hope that sparks some ideas for you. I am sure someone may have a more elegant solution.

Rajakumar Burra

Thanks @SteveF-Oracle

For sure your suggestions help. I will play around.

Meantime, I used Dataflow with Aggregate function. This seems to be working. I am validating.

Honestly I am more interested in calculation within workbook. With your ideas , i will explore little more and find out best practices.