topic Complex heirarchy solution for mixed granularity in QlikView

Complex heirarchy solution for mixed granularity

Mon, 22 Aug 2016 11:34:10 GMT

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

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 — Tue, 23 Aug 2016 08:55:25 GMT

Hi,

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

Can you explain an example?

Thanks.

Tue, 23 Aug 2016 09:00:42 GMT

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 — Tue, 23 Aug 2016 09:09:44 GMT

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

Location is not in the path.

Tue, 23 Aug 2016 10:16:55 GMT

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

The actual formulas in my case will be

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

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