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

[magnusrydberg]

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

[poojashribanger · Accepted solution]

you acn't use nested aggregation like

Sum(Count("UserId")) as UUniqueUser

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

[magnusrydberg]

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