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

Question

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

Rajakumar Burra · Answer

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.

Steve F-2 · Answer

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 · Answer

@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)