topic Re: Using Sum and Distinct in Set Analysis in QlikView

Using Sum and Distinct in Set Analysis

ZoeM — Sat, 16 Nov 2024 04:24:57 GMT

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!

Re: Using Sum and Distinct in Set Analysis

bharathadde — Mon, 18 Feb 2019 20:44:41 GMT

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

Re: Using Sum and Distinct in Set Analysis

ZoeM — Mon, 18 Feb 2019 21:00:25 GMT

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

Re: Using Sum and Distinct in Set Analysis

bharathadde — Mon, 18 Feb 2019 21:20:04 GMT

Just use this in text box to display the value

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

Re: Using Sum and Distinct in Set Analysis

ZoeM — Mon, 18 Feb 2019 21:32:58 GMT

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!

Re: Using Sum and Distinct in Set Analysis

ZoeM — Tue, 19 Feb 2019 15:17:56 GMT

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.