Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Marcoimp
Partner - Creator III
Partner - Creator III

Average Share in selected Period

How can I get this value? I Have a single field (named "ShareValue") for every day, not different field for each month as in the picture attached.

AverageShareFormula.png

Is there some stat function I can use, maybe with a set analysys?

Thank you for the help.

M.Imperiale
5 Replies
Nicole-Smith

See the expression in the text box in the attached .qvw

=

sum(aggr(sum(Value)*round(monthend(max(Date))-monthstart(min(Date))),Month))

/

round(monthend(max(Date))-monthstart(min(Date)))

Marcoimp
Partner - Creator III
Partner - Creator III
Author

Thank you Nicole,but:

1) What if the selection is Jan+Apr+Aug ?!

2) I can't use the round value of total days in months, I need to use the extact number (in my example 31+28+30, if Jan+Apr+Aug are selected then I need to take 31+30+31) in order to get the "real" average of the value...

Share =

(SUM(Values for Month1)*NumDays(Month1)) + (SUM(Values for Month2)*NumDays(Month2)) + ......

/

NumDays(Month1) + NumDays(Month2) + .....

Hope I was clear.

M.Imperiale
Nicole-Smith

1) Okay, slight change:

=

sum(aggr(sum(Value)*round(monthend(max(Date))-monthstart(min(Date))),Month))

/

sum(aggr(round(monthend(max(Date))-monthstart(min(Date))),Month))

2) You must use round to get the exact number of days.  If you don't use round, you'll get 30.99999 days for January instead of 31 because calculating the number of days it takes off one second (hence the .99999).

Example attached.

Not applicable

Hi,

Another possible formula:

= avg(aggr(Sum(Value),Date))

ecolomer
Master II
Master II

Hi, here you have an example (in Spanish, but is sample to convert)