Error using sum and count together with Group By in Qlik script

magnusrydberg — Wed, 22 Dec 2021 15:42:54 GMT

Hi,

My problem is that I want to calculate unique users per day from a userlogfile "cacore.EnvUsersLog" and save this value in a new field "UUniqueUser"

My sqript is as follows:

// User Log

LOAD "UserId" as UUserId,

LoginDate as ULoginDate,

Year(LoginDate) as UYear,

Week(LoginDate) as UWeek,

dual( Year(LoginDate) & '-' & Week(LoginDate), num(Year(LoginDate)) & num(Week(LoginDate), '00' )) as UYearWeek,

If(1=1,'Portal') as UType,

dual( Year(LoginDate) & '-' & Month(LoginDate), num(Year(LoginDate)) & num(Month(LoginDate), '00' )) as UYearMonth,

Sum(Count("UserId")) as UUniqueUser

Group By LoginDate;

SQL SELECT *FROM "myloc_PROD_220".cacore.EnvUsersLog;

I got the following error message

Error in expression: Nested aggregation not allowed : LOAD "UserId" as UUserId, LoginDate as ULoginDate, Year(LoginDate) as UYear, Week(LoginDate) as UWeek, dual( Year(LoginDate) & '-' & Week(LoginDate), num(Year(LoginDate)) & num(Week(LoginDate), '00' )) as UYearWeek, If(1=1,'Portal') as UType, dual( Year(LoginDate) & '-' & Month(LoginDate), num(Year(LoginDate)) & num(Month(LoginDate), '00' )) as UYearMonth, Sum(Count("UserId")) as UUniqueUser Group By LoginDate

What am i doing wrong?

Thanks in advance!

// Magnus

Re: Error using sum and count together with Group By in Qlik script

poojashribanger — Thu, 31 May 2018 10:07:48 GMT

you acn't use nested aggregation like

Sum(Count("UserId")) as UUniqueUser

instead just use Count("UserId") you can sum it up in frontend.

Re: Error using sum and count together with Group By in Qlik script

magnusrydberg — Thu, 31 May 2018 10:18:35 GMT

Thanks a lot! Sometimes it's more easier then you expect

topic Error using sum and count together with Group By in Qlik script in App Development

Error using sum and count together with Group By in Qlik script

Re: Error using sum and count together with Group By in Qlik script

Re: Error using sum and count together with Group By in Qlik script