Using Sum and Distinct in Set Analysis

[ZoeM]

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!

[bharathadde · Accepted solution]

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 attachments

[ZoeM]

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]

Just use this in text box to display the value

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

[ZoeM]

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]

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.