Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
mrbalyaqv
Contributor III
Contributor III

Expression with Aggr() calculates not for all rows in table chart

Hi all!

I have a following table chart:

QV_aggr.png

User is a field user_name;

Frequency is calculated dimension, which means a number of visits to contacts, made by user in period: Aggr(count(distinct visit_id), contact_id)

Contacts count is an expression. It shows how many contacts were visited with that frequency: count(distinct contact_id)

Test % should calculate % of contacts, visited with following frequency, of total number of contacts, visited by user. So, the result table should look like this:

UserFrequencyContacts countTest %
A1164164/(164+33) = 0.83
A23333/(164+33) = 0.17
B1150150/(150+9) = 0.94
B299/(150+9) = 0.06

Now for Test % I use this expression:

count(distinct contact_id) / sum(Aggr(count(DISTINCT contact_id), user_id ))

And as you can see from first picture, it works only for one row per each user.

Is there a way to find this Test % for all calculated dimensions?

Sample data and qvw-file are attached.

1 Solution

Accepted Solutions
sunny_talwar

Try this instead

=Count(DISTINCT contact_id)/count(DISTINCT TOTAL <user_name> contact_id)

or this

=Count(DISTINCT contact_id)/Aggr(NODISTINCT Count(DISTINCT contact_id), user_id)


Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Try this instead

=Count(DISTINCT contact_id)/count(DISTINCT TOTAL <user_name> contact_id)

or this

=Count(DISTINCT contact_id)/Aggr(NODISTINCT Count(DISTINCT contact_id), user_id)


Capture.PNG

mrbalyaqv
Contributor III
Contributor III
Author

Thank you!