Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Creator III
Creator III

Using Sum and Distinct in Set Analysis

Hello Community.

I am trying to create an expression that averages the number of days for a product from invoice to purchase. 

In the attached, I have about 17 products and associated # of Days to Purchase information. 

I am needing to average out the total number of days for the unique product (not considering that it is a duplicate) and divide it by the number of products (17 in this case). 

Using the average formula does not consider the zero values and I found that to be wrong based on our business case. The desired result is 18.75 average for the 16 products that meet this criteria. 

Any help rendered will be truly appreciated.

Thanks!

Labels (2)
1 Solution

Accepted Solutions
bharathadde
Creator II
Creator II

Hi attached file with a small change on the chart 

I switched Average Expression into Average instead of sum, you can find the change in the attachments1.JPG

View solution in original post

5 Replies
bharathadde
Creator II
Creator II

Hi attached file with a small change on the chart 

I switched Average Expression into Average instead of sum, you can find the change in the attachments1.JPG

View solution in original post

ZoeM
Creator III
Creator III
Author

Very interesting...

I am getting the result. So, would I also still get the same result if I used the AVG function and changed the Total Mode?

Also, how can I show this in a KPI text object? I am still getting a wrong value

bharathadde
Creator II
Creator II

Just use this in text box to display the value

=Avg({<ID={'ING'},[#of Days to Purchase]={'>=0'}>} [#of Days to Purchase])

ZoeM
Creator III
Creator III
Author

Thanks, I will have to play around with it a bit for my specific use case but it seems to work. 

Thanks for being prompt my friend!

ZoeM
Creator III
Creator III
Author

Hi,

In the attached, I need to show the number of unique products and sum of days to purchase for those unique products in KPI text objects. 

I was able to get the # unique products but I cannot get the number unique # days to purchase.

Any help rendered will be appreciated.