10 Replies Latest reply: Sep 23, 2013 5:16 AM by Thomas Jensen

# Average set analysis text box

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

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

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

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

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

What?? im not missing any days?

• ###### Re: Average set analysis text box

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

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

• ###### Re: Average set analysis text box

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

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.

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

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