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: 
bharatkishore
Creator III
Creator III

Need to calculate average

Hi All,

I have a straight table chart and in the presentation tab i have given max number as 10 values.

I have campaign and email send date as dimension and my expression is

sum([Unique Clickthroughs])/sum([Total Sends]).

Now by doing this i have got some values.

Now i needed an expression to calculate the avg of those 10 values.

Attached excel for reference and how i am calculating  the values.

Thanks

Bharat

1 Solution

Accepted Solutions
sunny_talwar

Got it

Try this:

If(Num(Rank(TOTAL If(Sum([Unique Clickthroughs]) > 0 and Sum([Total Sends]) > 0, [Email Sent Date]), 4, 1)) <= 10,

If(Change <= -0.10, 'qmem://<bundled>/BuiltIn/led_r.png',

If(Change <= 0.10, 'qmem://<bundled>/BuiltIn/led_y.png', 'qmem://<bundled>/BuiltIn/led_g.png')))

View solution in original post

28 Replies
vishsaggi
Champion III
Champion III

May be try this: Change accordingly.

= Num(Avg(Total  [Unique Open Rate]), '#,##0.00%')

bharatkishore
Creator III
Creator III
Author

Tried.. but no value is coming..

vishsaggi
Champion III
Champion III

Can you share your sample app if possible?

bharatkishore
Creator III
Creator III
Author

please check the attached qvw and excel which values should come and for the calculation also.

vishsaggi
Champion III
Champion III

Bharat, I think the values from excel sheet you sent works fine with the initial expression i sent, but there are synthetic keys in your data model. Can you send the actual two excel files you are loading into this qvw file if possible ?

bharatkishore
Creator III
Creator III
Author

You can remove the second tab in script.

I have tried with the same expression but it is not working for me.

If possible can u please send me the file. so tht i can check

vishsaggi
Champion III
Champion III

I have just used your excel sheet and the qvw file and it worked.

Please check and let me know.

maheshkuttappa
Creator II
Creator II

Below expression should work

Num(([Unique Open Rate]-Avg(TOTAL [Unique Open Rate]) )/Avg(TOTAL [Unique Open Rate]),'###.##%')

Greg_Williams
Employee
Employee

Does this address your request?

Perhaps, if not, consider AGGR function, or TOTAL added to your sum or avg function.

-gw