Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qvraj123
Contributor II

SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

Hi All, thanks for your time, I get error in expression with the below - what is that I am missing could not figure out - I want to count then do a sum of the count - sounds simple but missing something

SUM(COUNT(IF(SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA',EMPNO)))

Thanks a lot for your time

1 Solution

Accepted Solutions
Employee
Employee

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

You can´t use an aggregation function insider another, you must have an AGGR()

SUM(AGGR(COUNT({<SOURCE={'SMS_DATA' ,'APPLE_DATA' ,'MDM_DATA'>},EMPNO)),<A field>)

View solution in original post

6 Replies
ramoncova06
Valued Contributor III

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

you are missing an aggr

sum(aggr(count({<Source ={'SMS_DATA','APPLE_DATA',MDM_DATA}>}EMPNO),EMPNO))

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

Hi Rajender, you don't need the sum, if your count returns 5, you're doing sum(5), anyway doing a SUM(COUNT()) will return an error, try with:

COUNT(IF(SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA',EMPNO))

or

COUNT(DISTINCT IF(SOURCE='SMS_DATA' OR SOURCE='APPLE_DATA' OR SOURCE='MDM_DATA',EMPNO))


Or tell us what are you trying to sum

qvraj123
Contributor II

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

Hi Ramon

I really appreciate your time, but why would I need the AGGR here - I just want to get the count for that criteria and sum it - little confused - my EMPNO filed includes data like 001234, test12,genericid etc

do I need to use num() after the count

thanks again

Raj

Employee
Employee

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

You can´t use an aggregation function insider another, you must have an AGGR()

SUM(AGGR(COUNT({<SOURCE={'SMS_DATA' ,'APPLE_DATA' ,'MDM_DATA'>},EMPNO)),<A field>)

View solution in original post

qvraj123
Contributor II

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

Hi Ruben

Thanks for your time, I thought I would get the count and do a sum - looks like I cannot use an aggregate function inside another aggregation function without the AGGR function - thanks a lot

Employee
Employee

Re: SUM(COUNT(IF( ) ) ) - What is wrong with this expression?

That´s correct, anytime you need nest a function (SUM,COUNT, etc) insider another, you MUST use an AGGR to indicate the "group by" (which field you´re rolling up)