Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:Capture.PNG.png

Test straight table is correct.

1 Solution

Accepted Solutions
qlikpahadi07
Specialist
Specialist

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

View solution in original post

10 Replies
jolivares
Specialist
Specialist

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

Not applicable
Author

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
Master II
Master II

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

Not applicable
Author

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
Partner - Champion
Partner - Champion

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

Not applicable
Author

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

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

Not applicable
Author

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

Not applicable
Author

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