Average set analysis text box

[#1]

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.

[jolivares]

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

[#3]

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

[whiteline]

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

[#5]

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)

[CELAMBARASAN]

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

[#7]

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

[qlikpahadi07 · Accepted solution]

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

[#9]

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

[#10]

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