topic Average Share in selected Period in QlikView

Average Share in selected Period

Marcoimp — Thu, 23 Jan 2014 12:00:53 GMT

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.

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

Thank you for the help.

Re: Average Share in selected Period

Nicole-Smith — Thu, 23 Jan 2014 14:34:01 GMT

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

Re: Average Share in selected Period

Marcoimp — Thu, 23 Jan 2014 15:29:27 GMT

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.

Re: Re: Average Share in selected Period

Nicole-Smith — Thu, 23 Jan 2014 15:36:42 GMT

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.

Re: Average Share in selected Period

Thu, 23 Jan 2014 15:42:15 GMT

Hi,

Another possible formula:

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

Re: Average Share in selected Period

ecolomer — Sun, 31 Aug 2014 07:00:15 GMT

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