topic Re: Help: Sum(value/ Count Distinct) in QlikView

Help: Sum(value/ Count Distinct)

Wed, 25 Nov 2020 16:16:04 GMT

I cant figure this out, what am i doing wrong?

When i do it like this i have no problems, i get the right numbers, the 11 is based count of the months.

sum(value)/sum(numbers/11))

But when i try it like this based on the months i have selected its not working:

sum(value)/sum(numbers/count(DISTINCT monthnumber))

anyone?

Re: Help: Sum(value/ Count Distinct)

Chanty4u — Fri, 25 Mar 2016 16:09:17 GMT

can share ur sample app?

r u trying in chart or text object?

Re: Help: Sum(value/ Count Distinct)

Gysbert_Wassenaar — Fri, 25 Mar 2016 16:10:14 GMT

You're nesting aggregation functions. That's possible but then you need to specify which dimensions need to be used to create a list of counts of monthnumbers. You can use the aggr function for that:

sum(value)/sum(numbers/aggr(count(DISTINCT monthnumber),MyDim1, MyDim2, ... MyDimN))

Re: Help: Sum(value/ Count Distinct)

sunny_talwar — Fri, 25 Mar 2016 16:11:56 GMT

May be this:

Sum(value)/(Sum(numbers)/Count(DISTINCT monthnumber))

Re: Help: Sum(value/ Count Distinct)

Ireneusz_Łomzik — Fri, 25 Mar 2016 16:15:02 GMT

use aggregation function

Re: Help: Sum(value/ Count Distinct)

Fri, 25 Mar 2016 16:34:01 GMT

‌pivot table

ill add a sample soon

Re: Help: Sum(value/ Count Distinct)

Fri, 25 Mar 2016 16:35:56 GMT

i tried this, its giving me the wrong output

‌

Re: Help: Sum(value/ Count Distinct)

Fri, 25 Mar 2016 18:00:06 GMT

this is how it looks like:

number:

sum(number)/count(DISTINCT monthnumber)

total value:

sum(value)

im trying to calculate price just like the "number" expression:

its only working like this > sum(value)/sum(numbers/11))

the expressions gives me an error when i put it like this>

sum(value)/sum(numbers/count(DISTINCT monthnumber))

i wanna use the count(distinct monthnumber) function to calculate the price based on YTD