Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Complex heirarchy solution for mixed granularity

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

4 Replies
Highlighted
Specialist
Specialist

Re: Complex heirarchy solution for mixed granularity

Hi,

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

Can you explain an example?

Thanks.

Highlighted
Not applicable

Re: Complex heirarchy solution for mixed granularity

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

Highlighted
Specialist
Specialist

Re: Complex heirarchy solution for mixed granularity

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

Location is not in the path.

Highlighted
Not applicable

Re: Complex heirarchy solution for mixed granularity

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