Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author


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.

Not applicable
Author

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.

Not applicable
Author

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?