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

    Average set analysis text box



      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.



      Test straight table is correct.

        • Re: Average set analysis text box
          Juan Olivares

          Try this expression in one text box and what happens:




          (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
            Lester Rosete

            Hi Thomas,


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

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







            • Re: Average set analysis text box
              whiteline _



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

              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)


              • 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
                  Celambarasan Adhimulam

                  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

                    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.



                    • Re: Average set analysis text box
                      Rohit Koul

                      Hi thomas jensen ,


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