topic Re: Average set analysis text box in QlikView

Average set analysis text box

Sun, 22 Sep 2013 14:29:39 GMT

hello

I have a average problem. I have attached a screenshot and a sample.

Try to select customer bbtkljaorrx.

Then in the big straight table you get a TOTAL of 260.293 sessions which is based on all 3 months (jul,aug,sep)

To calculate Monthly Average i need a weighted average, due to september is not completed. Therefore i take this calculation to determine average on a month which is not done TotalSession / (Total days with data / with total days in month)

But as you can see on my textbox my set analysis expressions calculates my weigthed average on all months which means it takes

260.293 / (83/92) which = 288.518

Divivde 288.518 with total months to find Monthly average = 288.518 / 3 = 96173 - But this is wrong.

My total should be as in my TEST straight table (82771+101200+109028) = 292.999 / 3months = 97.667.

Does anyone know what if have done wrong in my set analysis? Cause it only calculates correctly when i choose a month /but then i cannot calculate the average in another textbox.

Sample attached.

Screenshot:

Test straight table is correct.

Re: Average set analysis text box

jolivares — Mon, 23 Sep 2013 02:48:55 GMT

Try this expression in one text box and what happens:

=num(Avg(Aggr(sum(TotalSessions)

(count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /

(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))),DimRaptorCustomer.CustomerID,MonthYear)),'#.##0')

Re: Average set analysis text box

Mon, 23 Sep 2013 03:27:43 GMT

Hi Thomas,

Try adding Avg ( Aggr ( ....."your expression"....), MonthYear)) - For Monthly Avg

and add Sum ( Aggr ( ......."your expression"...), MonthYear)) - For Total of months

Regards,

Lester

Re: Average set analysis text box

whiteline — Mon, 23 Sep 2013 06:58:38 GMT

Hi.

Use aggr() to calculate the average of monthly sums explicitly:

=Avg(
aggr(
sum(TotalSessions) / (count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /
(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))) / Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct MonthFact)
, MonthYear))

Re: Average set analysis text box

Mon, 23 Sep 2013 07:59:49 GMT

You need to know what happens when you use MonthEnd():

At first, the following might seem strange. e.g. for August:

Min(Date_CleanDato) = 2013-08-01

Max(Date_CleanDato) = 2013-08-31

MonthStart(Max(Date_CleanDato)) = 2013-08-01

MonthEnd(Max(Date_CleanDato)) = 2013-08-31

Max(Date_CleanDato)-Min(Date_CleanDato)+1 = 31

MonthEnd(Max(Date_CleanDato))-MonthStart(Max(Date_CleanDato))+1 = 32

This is because MonthEnd() will return a timestamp:

MonthEnd(Max(DateCleanDato)) = 2013-09-30 23:59:59

This is a problem when you evaluate it as a number:

MonthEnd(Max(DateCleanDato)) = 41547,9999999

To properly evaluate it as a date, first you need to Floor() it:

Floor(MonthEnd(Max(DateCleanDato))) = 41547

Date(Floor(MonthEnd(Max(DateCleanDato)))) = 2013-08-31

(Finally, don't forget the +1 or you will miss 1 day of the month)

Re: Average set analysis text box

CELAMBARASAN — Mon, 23 Sep 2013 08:30:48 GMT

Try with using Aggr

=Sum(Aggr(sum(TotalSessions) / (count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /

(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))) / Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct MonthFact), MonthYear)) / 3

Re: Average set analysis text box

Mon, 23 Sep 2013 08:40:46 GMT

Hi Thomas

At a quick glance (I couldn'd get the numbers working to replicate what you have above), but I believe the issue lies in the end of the formula. If you look at the formula it is applying the division by MonthFact last - check the position of your brackets.

WIthout getting the model to work it's difficult to fix, but you could try;

=num(sum(TotalSessions) / (count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /

sum((Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))) / Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct MonthFact)),'#.##0')

Let me know how you get on.

Steve

Re: Average set analysis text box

qlikpahadi07 — Mon, 23 Sep 2013 08:45:34 GMT

Hi TJETJETJE ,

I must say you have done a very complex mapping

anyways here is the solution for Avg you need to aggregate the result as in Straight table it automatically done as per dimension so in Text Box you need to aggregate the result.

your exp will be Avg(agg(<Expersion>),Dimension))

try this in Text box

=Avg(aggr(sum(TotalSessions) / (count({<DimRaptorCustomer.CustomerID={'$(vCustomer)'},Date_CleanDato={">=$(=Date(Min(Date_CleanDato))) <= $(=Date(Max(Date_CleanDato)))"}>} DateKey) /

(Monthend(Max(Date_CleanDato)) - MonthStart(min(Date_CleanDato)))) / Count({1<DimRaptorCustomer.CustomerID={'$(vCustomer)'}>}distinct MonthFact),MonthYear))

Re: Average set analysis text box

Mon, 23 Sep 2013 09:02:48 GMT

Why divide by 3 at the end? It cannot be static.

Re: Average set analysis text box

Mon, 23 Sep 2013 09:07:48 GMT

Damn, thank you! I was close to rip my head off! However i did think of aggr today, but you were faster than me! Great work, and many thanks for the solution.

I appreciate it!

have a nice day

Re: Average set analysis text box

Mon, 23 Sep 2013 09:12:33 GMT

What?? im not missing any days?