Complex heirarchy solution for mixed granularity

[#1]

I'm analyzing Google AdWords reports against client registrations, retention, etc.

There are a lot of Report Types  |  AdWords API‌ as data sources with difference granularity. For example

• Date, Campaign, cost, clicks (worldwide)
• Date, Campaign, Location, cost, clicks
• Date, Campaign, AdGroup, cost, clicks
• Date, Campaign, AdGroup, Keyword, cost, clicks
• Date, Campaign, AdGroup, Ad, cost, clicks

Clients match by their Locations, Campaigns, AdGroups, Ads and Keywords.

There would be a lot of dependencies like

if(GetSelectedCount(Location)>0, sum({<Type={'Report_Location'}>} cost) ,

                                                              if(GetSelectedCount(AdGroup)>0,sum({<Type={'Report_AdGroup'}>} cost) ,

                                                                                                                            sum({<Type={'Report_Campaign'}>} cost)) )....

too difficult to maintain for every table dimension

So I think to add Depth field for filtering

sum({<Depth={$(=min(Depth))}>} cost)

Unfortunately set analysis doesn't work properly.Here is example attached.

Please help

[Anonymous]

Hi,

I saw your application, but I didn't understand what is your desired output.

Can you explain an example?

Thanks.

[#3]

There is SUM(costs) on every level. I can't summarize all levels because it will multiply the total because it cumulative.

What is why I'm using sum({<Depth={$(=min(Depth))}>} cost) formula.

It works on 1st levels but show 0 on next

[Anonymous]

Maybe the problem is that Report_Campaing and Report_Location have the same depth = 1.

Location is not in the path.

[#5]

AdWords structure is complex. To avoid many conditions in every expressions I'm reducing paths number

was

Network Type - Campaign --- Location

                                      \--- AdGroup --- Keyword

                                                  \--------  Ad

become

• Network Type --> Campaign (<any> + Location) --> AdGroup --> Keyword + Ad

The actual formulas in my case will be

=IF( GetSelectedCount(Location)>0, SUM({<Depth={$(=min(Depth))}>} cost),

                                                     SUM({<Location={'<any>'},Depth={$(=min(Depth))}>} cost) )