Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
magnusrydberg
Partner - Contributor II
Partner - Contributor II

Using the average function of a sum in frontend

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....

2018-08-14_17-09-12.jpg

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

1 Solution

Accepted Solutions
juraj_misina
Luminary Alumni
Luminary Alumni

Hi Magnus,

sorry, my bad, I had a typo in one of expressions:

Avg(Aggr(Sum(UUniqueUserPortal)+Sum(UUniqueUserApp), UYearMonth))

This should work.

Juraj

View solution in original post

13 Replies
marcus_sommer

I think you need an aggr() for it, maybe:

avg(aggr(sum(UUniqueUserPortal) + sum(UUniqueUserApp), Dim1, Dim2))

whereby Dim1 and Dim2 are placeholder for the dimensions in whose context the calculation should be done (usually this are the used dimensions within the chart).

- Marcus

magnusrydberg
Partner - Contributor II
Partner - Contributor II
Author

Hello Marcus,

Thanks a lot for your suggestion but it didn't help, unfortunately.

I used the code in for the expression in frontend:

2018-08-15_17-15-27.jpg

But I do not get any average value when I run my report.

Any other suggestion or can I modify the sqript somehow?

balabhaskarqlik

May be this:

Avg(Aggr(Sum(UUniqueUserPortal) + Sum(UUniqueUserApp), UUniqueUserPortal, UUniqueUserApp))

Or

RangeAvg(UUniqueUserPortal,UUniqueUserApp)

juraj_misina
Luminary Alumni
Luminary Alumni

Hi,

for this to work correctly you would need to make sure that both UUniqueUserPortal and UUniqueUserApp have values on a single line, like this:

UYearWeekUUniqueUserPortalUUniqueUserApp
2018-2211
2018-2321
2018-2413

I suppose this is not true in your case and you have something like this

UYearWeekUUniqueUserPortalUUniqueUserApp
2018-221NULL
2018-22NULL1
2018-232NULL
2018-23NULL1


In Qlik Null+1=Null, therefore in your expression Avg(Null+1)=Avg(Null)=Null. You could either adjust your data model by using Join or try using

RangeAvg(Sum(UUniqueUserPortal), Sum(UUniqueUserApp))

or

Avg(Aggr(Sum(UUniqueUserPortal), Sum(UUniqueUserApp), UYearWeek))

depending on what you need to achieve.


Hope this helps.

Juraj



marcus_sommer

You need to replace the Dim1,Dim2 placeholder with the real fields in which context the calculation should be done.

- Marcus

magnusrydberg
Partner - Contributor II
Partner - Contributor II
Author

Hello Juraj,

Thanks for your help! But I do not really succeed unfortunately...

I've tried to adjust my data by using join and also used the Avg(Aggr(Sum(UUniqueUserPortal), Sum(UUniqueUserApp), ULoginDate)) but I got no results..

When I Use the command RangeAvg(Sum(UUniqueUserPortal), Sum(UUniqueUserApp)) in my expression I've got some figures but that not seems to be the average value I want to see.


I really want to present the average value of number of unique persons per day that logged in to the system. In my case this information is separated in 2 files in the system, cacore.EnvUsersLog for portal users and OTSDATA.UserLoginLog for app users. In my sqript I've done a calculation of nuber of users per day using command Group by  LoginDate.


My sqript look like this now:

// User Statistics

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(DISTINCT "UserId")) as UUniqueUserPortal

  Group By LoginDate;

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


Join

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(DISTINCT "UserID")) as UUniqueUserApp

    Group By LoginDate,SystemType;

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



So the problem is what expression should I use in front end to present the mean value of number of users per day during a month. (I also have another graph presenting the maxvalue of users per day during a month). Graphs look like this:

2018-08-16_13-46-53.jpg

The expression I've tried that gives any result is: RangeAvg(Sum(UUniqueUserPortal), Sum(UUniqueUserApp)) but this is not the correct mean value(see above). Using the Avg(Aggr(Sum(UUniqueUserPortal), Sum(UUniqueUserApp), ULoginDate)) returns no values at all...

So please do you have any other suggestion what expression I should use?

2018-08-16_13-54-58.jpg

Best regards

// Magnus

juraj_misina
Luminary Alumni
Luminary Alumni

Hi Magnus,

sorry, my bad, I had a typo in one of expressions:

Avg(Aggr(Sum(UUniqueUserPortal)+Sum(UUniqueUserApp), UYearMonth))

This should work.

Juraj

jonathandienst
Partner - Champion III
Partner - Champion III

Your join will act like a concatenate due to different values in he UType field. So the values never align. I do believe that Marcus' design pattern is correct,

avg(aggr(sum(UUniqueUserPortal) + sum(UUniqueUserApp), Dim1, Dim2))

but it is important to have the correct dimensions for the aggr(). What is correct is dependent on the data model and the charts. At a minimum, the dimensions should include the chart's dimension(s).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
magnusrydberg
Partner - Contributor II
Partner - Contributor II
Author

Hi Again,

Now I get a value but it seems not to be an average value per day...... It seems more like a sum for the actual month. The value for august should be about 150-160 unique users but the graph shows 1629.....

2018-08-16_14-39-31.jpg

Any more ideas?

// Magnus