Using the average function of a sum in frontend

[magnusrydberg]

Hello,

I'm trying to use the average function in the frontend to calculate the average value for a sum of two fields.

My expression in frontend looks like this: Avg(UUniqueUserPortal+UUniqueUserApp).

I've got no values by this expression....

What am I doing wrong in my expression? (When using only one variable at a time I've gott an average value)

The two fields UUniqueUserPortal and UUniqueUserApp come from two different files. My sqript look like this:

LOAD

    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,

  If(num(weekday(LoginDate))<>6 and num(weekday(LoginDate))<>7,Count("UserId")) as UUniqueUserPortal

  Group By LoginDate;

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

LOAD

    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,

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

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

    SystemType as USystemTyp,

    If(num(weekday(LoginDate))<>6 and num(weekday(LoginDate))<>7,Count("UserID")) as UUniqueUserApp

    Group By LoginDate,SystemType;

  SQL Select *FROM "myloc_PROD_220".OTSDATA.UserLoginLog;

What am

[magnusrydberg]

You are so right!

I've changed the expression to:

Avg(Aggr(Sum(UUniqueUserPortal)+Sum(UUniqueUserApp),ULoginDate, UYearMonth)) and now it seems to work!

I Suppose Marcus and Jurij meant so also but I misunderstood..

Thank a lot to all of you!

// Magnus

[magnusrydberg]

Thanks a lot Jurij!

Now it works with the expression: Avg(Aggr(Sum(UUniqueUserPortal)+Sum(UUniqueUserApp),ULoginDate, UYearMonth))

I've learnt a lot

// Magnus

[juraj_misina]

I'm glad it worked. Take care.

Juraj

[Qstradamus]

How would you exclude 0 (zero) values in this function?
(I have a similar expression)