Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bharatkishore
Contributor 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

Re: Need to calculate average

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')))

28 Replies
vishsaggi
Esteemed Contributor III

Re: Need to calculate average

May be try this: Change accordingly.

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

bharatkishore
Contributor III

Re: Need to calculate average

Tried.. but no value is coming..

vishsaggi
Esteemed Contributor III

Re: Need to calculate average

Can you share your sample app if possible?

bharatkishore
Contributor III

Re: Need to calculate average

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

vishsaggi
Esteemed Contributor III

Re: Need to calculate average

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
Contributor III

Re: Need to calculate average

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
Esteemed Contributor III

Re: Need to calculate average

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

Please check and let me know.

mahesh3k
Contributor

Re: Need to calculate average

Below expression should work

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

Employee
Employee

Re: Need to calculate average

Does this address your request?

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

-gw

Community Browser