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

Granularity problem between two tables: How to solve it ? + Set Analysis

Histogram 2014-06-23.jpg

I made a simple example in which I removed everything which is not needed to show the problem I'm facing in QlikView.

I have two tables: MonthlySummary, vwAppUsageDevice. And I have a Link table which is filled with data from these two tables.

The linktable has a LinkKey which consists of a concatenation of the following: DateId & '-' & CountryID & '-' & DeviceTypeID & '-' & ServiceId AS LinkKeyID

As MonthlySummary does not have a ServiceID while AppUsageDevice has, I have created a LinkKey consisting of: DateId & '-' & CountryID & '-' & DeviceTypeID & '-' & '' AS LinkKeyID

For your info: a ServiceID is just an App Name like 'Youtube'. And DeviceTypeID is just a range of devices.

I want to visualize a ratio (a percentage) over time, answering the following question: How many users that launch the Dashboard (= Use the equipment) use a certain App in a certain country in a certain period (month), on a certain Device range (DeviceTypeID).

I have created a chart for this with Dimension: DateID    And Expression:  SUM(AppUsage) / SUM(DashboardVisits)

And then in QlikView I select an App (a ServiceID) (e.g. Youtube), I select a Country e.g. The Netherlands, I select a Month e.g. June 2014 and a specific device range (DeviceTypeID).

If I select a certain country, date, devicetypeID, the chart does show information. The real problem is that this will not work as because MonthlySummary has no ServiceID, the chart will return 'no data available' as soon as I select a ServiceID e.g. Youtube.

My question: How do I work around this?

If I would select: Youtube, The Netherlands, a certain DeviceTypeID and Month (June 2014), for 'SUM(DashboardVisits)' I do want to ignore the fact that I selected a specific ServiceID, but I do want to see the DashboardVisits limited to only the Netherlands (so not for all countries etc.), for a certain DeviceTypeID for the specific Month.

How do I do that?

I have tried several expressions like e.g.:

1) SUM(AppUsage) / SUM( {  } DashBoardVisits )

2) SUM(AppUsage) / SUM( { $ } DashBoardVisits )

At first side this seems to work (although I don't really understand that both expressions seem to do exactly the same. But as the ServiceID is actually the key to the Service table, I was expecting that filtering on another attribute in the Service table would also work e.g. ServiceName. This unfortunately does not seem to work in the expressions above.

Of course I can do something like this:

1) SUM(AppUsage) / SUM( {} DashBoardVisits )

2) SUM(AppUsage) / SUM( { $ } DashBoardVisits )

But this does not really fix the problem, as there are other attributes in the Service table as well where I want to filter on.

And the Service table has so many attributes that it is not practical to include all the attributes in a similar statement like mentioned above.

But, how do I solve it then?

And by the way what does "SUM( { $ } DashBoardVisits )" do? I first though I needed that but this does not seem to work and I can't figure out what it ought to do.

3 Replies
Highlighted
Specialist
Specialist

Re: Granularity problem between two tables: How to solve it ? + Set Analysis


to ignore an attribute selection (ignore ServiceID), the set analysis should look something like:

sum({<ServiceID=>} DashBoardVisits)

"ServiceID=" tells that expression to ignore any ServiceID selections. The sum will change for other attribute selections for example, country.

Highlighted
Not applicable

Re: Granularity problem between two tables: How to solve it ? + Set Analysis

I see something went wrong while copy/pasting in the message on the Qlik community.

The statements:

1) SUM(AppUsage) / SUM( {  } DashBoardVisits )

2) SUM(AppUsage) / SUM( { $ } DashBoardVisits )

should have been:

1) SUM(AppUsage) / SUM( {<ServiceID=>} DashBoardVisits )

2) SUM(AppUsage) / SUM( {$<ServiceID>} DashBoardVisits )

and the last 2 statements in my message should have been:

1) SUM(AppUsage) / SUM( {<ServiceID=, ServiceName=>} DashBoardVisits )

2) SUM(AppUsage) / SUM( {$<ServiceID, ServiceName>} DashBoardVisits )

And there is the last question asking what " SUM( {$<ServiceID=>} DashBoardVisits ) " is expected to do.

Highlighted
Not applicable

Re: Granularity problem between two tables: How to solve it ? + Set Analysis

QV granularity problem 2014-06-04.jpg

@jsaradhi: Thanks for the response.

Attached you find my simplied QV model.

As you can see the Service table has ServiceID and ServiceName, but in practice it has like 25 other attributes as well.

If I would have only ServiceID and ServiceName I could do easily do a "SUM {<ServiceID=, ServiceName=> } DashBoardVisits )", but is there a way to prevent having to add all attributes?

With other words: Is there no way to tell QlikView that ServiceID shall be ignored but also all other attributes that would limit ServiceID?