Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ZoeM
Contributor 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
Contributor II

Re: Using Sum and Distinct in Set Analysis

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
Contributor II

Re: Using Sum and Distinct in Set Analysis

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
Contributor III

Re: Using Sum and Distinct in Set Analysis

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
Contributor II

Re: Using Sum and Distinct in Set Analysis

Just use this in text box to display the value

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

ZoeM
Contributor III

Re: Using Sum and Distinct in Set Analysis

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
Contributor III

Re: Using Sum and Distinct in Set Analysis

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.