topic using sum distinct value in set analysis in QlikView

using sum distinct value in set analysis

suzzymay — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Trying to total up pool size for the most current date, but have duplicate records.

Date IP_pool Pool_size

20190915 1.120.0.0/20 4093

Sum({<DynamicDateNum = {"$(=Max(DynamicDateNum))"}>}Pool_size)

This sums up all columns for date.

Re: using sum distinct value in set analysis

Vegar — Sun, 15 Sep 2019 07:44:32 GMT

I would suggest to try identifying the data you want to calculate by other means than their distinct values.

To calculate distinct use this

Sum({<DynamicDateNum = {"$(=Max(DynamicDateNum))"}>}distinct Pool_size)

You could also be helped by an only calculation.

Only({<DynamicDateNum = {"$(=Max(DynamicDateNum))"}>}Pool_size)

Re: using sum distinct value in set analysis

StarinieriG — Mon, 16 Sep 2019 07:57:52 GMT

Hi,

you could try with this expr:

Sum({<DynamicDateNum = {"$(=Max(DynamicDateNum))"}>}Aggr(Max(Pool_size),Date))

Re: using sum distinct value in set analysis

sunny_talwar — Mon, 16 Sep 2019 11:01:29 GMT

I would try this

Sum({<DynamicDateNum = {"$(=Max(DynamicDateNum))"}>} Aggr( Only({<DynamicDateNum = {"$(=Max(DynamicDateNum))"}>} Pool_size) , DynamicDateNum, IP_pool))

Re: using sum distinct value in set analysis

suzzymay — Tue, 17 Sep 2019 03:54:26 GMT

Thankyou..