Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help: Sum(value/ Count Distinct)

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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))


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Chanty4u
MVP
MVP

can share ur sample app?

r u trying in chart  or text object?

Gysbert_Wassenaar

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))


talk is cheap, supply exceeds demand
sunny_talwar

May be this:

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

Ireneusz_Łomzik
Employee
Employee

use aggregation function

Not applicable
Author

‌pivot table

ill add a sample soon

Not applicable
Author

i tried this, its giving me the wrong output

Not applicable
Author

Knipsel2.PNG

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