Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Counting entries of key values

Hi,

I have two tables

Presence

DateUser

Presence

111
120
211
221

and

Journal entries

DateUserSomeValue
11Some
21Things

The Presence table is there to give information about which journal entries should be taken into consideration in certain aggregations. 'Active' users are indicated by a 1 in the Present-field.

Some Users were not "active" at certain points in time, and so the average entries/users should not take these into consideration for these dates.

In particular, I'm looking to calculate the average of[ (users who used the journal on a date)/(active users on that date) ] over all dates.

My first strategy was to do ( Count Distinct Journal entries.User ) / ( { < Presence = {0} > } Count Distinct Presence.User )

However, this doesn't seem to give the desired outcome.

Is there a simple way to access only the Users that are present in one of the tables?

1 Reply
Highlighted
MVP
MVP

Re: Counting entries of key values

Not sure I entirely follow, but perhaps this?

count(distinct User) / count({<Presence={'1'}>} total <Date> distinct User)

As an aside, you have a synthetic key, which seems to be the entire key to both tables, so it seems like Presence and SomeValue should just be two fields on the same table.