3 Replies Latest reply on Dec 7, 2018 4:09 PM by Michel Caissie

    Complex AVG Calculation Issue

    antonio.rangelov

      Hi Guys,

      I am trying to create an AVG of 6months calculation and include it in visualizations and formulas.

      I have included a tbwx (v. 10.5.9)

      What I want to achieve is a ACG of the last 6 months, but this has to be a moving average, because I also want to have a Date field from which to select a Month.

      After selecting the desired Month I would like to have a measure that calculates the values only for this month and another measure that calculates an AVG of the last 6 months.

      For example if i have selected Month 7. I would like to have an average of the sum of the measure for Months 2, 3 ,4, 5, 6, 7 - this is last 6 months I refer to.

      Furthermore, this all has to be calculated on an Id level (customer id). These 2 measures I need to visualize as a scatter plot as well.

       

      what to do.jpg

        • 1. Re: Complex AVG Calculation Issue
          Michel Caissie

          Antonio,

           

          Since only the November data is available for the view, it will be difficult to compute the last 6 month average, even using lod  since the last month is a selection.

          What I suggest is that you use a parameter for the month selection,  and create a value 1 calculation based on the parameter selection.

          This way you filter at the calculation level, and not using the global filter shelf,  allowing to have different month filtering for each calc.

           

          In the attached, I was not sure  what avg exactly  you want for  value 3, so I did the avg of the monthly sum of value 3.

          • 2. Re: Complex AVG Calculation Issue
            antonio.rangelov

            Thank you for your help. You have given some nice advice, however the end result is not what I am looking for, we are almost there

            Maybe I didn't explain things well

            First of all, the calculation for the 6 month average Value 3 has to be for each Client Id. For example Customer X has:
            July = - 1236
            Aug = 329
            Sept = 18
            Oct = - 1840
            Nov = 86

            Dec = 41

            The sum of all of these months = -2602 , the 6 month average for this client is thus = -2602 / 6 = -433,67

            I need to achieve this number for all of the clients and then use it in the Types formula that will be used in sheet 1 to colour the graph or also in a table with all the other calculations like sum(value 3), avg 6 month value 3 and so on...

            In your example this avg = 358, which is quite far. I have added in the LOD formula the Client Id as well, but the avg = -498, which is still incorrect.

            (your example is red, what i tried to fix is green)

            avg case.jpg

             

             

            The other big problem here is ther date param. This means that if I have data for 3 years and thus 36 months I wll have to manually add them all. Also when a new month comes I will have to add it manually as well everytime, is that right? Imagine doing this every month for 20 clients

            • 3. Re: Complex AVG Calculation Issue
              Michel Caissie

              Antonio,

               

              Actually, there is another solution for this, which I should have though at first

              One way to make computation on data that is filtered out of the view, is to use table calcs as filters,  and also a table calc for the measure (value 3 avg in this case).

              When a table calculation is on the filter shelf, all other table calc are computed before the filtering.

               

              If you check on the sheet ValidateData,

              I first start to compute the  Monthly SUM(value 3) per Id with

              {FIXED [Client Id],DATETRUNC('month', [Date] ): SUM( [Value 3] )}

               

              and using a table calculation I compute the last 6 month average with

              WINDOW_AVG(MIN( [Value 3 (Customer monthly)] ),-5,0)

              computing on  Id, month  restarting on every Id.

               

              Next I create a Year and a Month  filter using

              LOOKUP(MIN(DATENAME('year',[Date])),0)

              LOOKUP(MIN(DATENAME('month', [Date] )),0)

               

              You can see the result on FinalView.

              The filters compute  Table accross

              You need the Id and Month in the Detail.

              Make sure to set the computing on Value 3 (last 6 month avg)    and also on Types.