1 Reply Latest reply: May 31, 2018 6:18 AM by Magnus Rydberg RSS

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

    Magnus Rydberg

      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