3 Replies Latest reply on Apr 20, 2018 6:48 AM by Vladislav Zaimov

    RUNNING_AVG in LOD

    Vladislav Zaimov

      Hello experts,

      I need some help with LOD calculation.

       

      In the attached file I need to calculate the running average per ID (for each day to have first the average for each ID of all data between the frirst day and the current day and second to average all IDs).

      I tried with RUNNING_AVG({include [ID]: RUNNING_AVG([Amount])})  and also tried {include [ID]: RUNNING_AVG([Amount])} , but there is an error "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources."

       

      If I change it like this RUNNING_AVG({include [ID]: RUNNING_AVG(AVG([Amount]))}) then the error is "Level of detail expressions cnnot contain table calculation or the ATTR function"

       

      If I want to calculate the average for each day then this expression  {include [ID]: AVG([Amount])} is working, but I can't chnage it to be running average.

       

      I also tried RUNNING_AVG(AVG({include [ID]: AVG([Amount])})) and it is valid, but it does not calculate what I need.

       

      And I also want to calculate the percentile of the running average. PERCENTILE([Avg Amount per ID],0.25)

       

      More simply explained:

      I know how to calculate these two numbers for each day:

      Avg Amount per ID = {include [ID]: AVG([Amount])}

      A1 = PERCENTILE([Avg Amount per ID],0.25)

       

      but I can't calculate them with running average ( values for each day to be calculated from the data since the beginning to each of these days )

       

      Very complicated, I hope somebody will understand it

        • 1. Re: RUNNING_AVG in LOD
          Jim Dehner

          Good morning

          Not certain I fully understand but see the attached approach

           

          I did it in table form so I can see the data but you can change it to a plot

           

          this is the formula - since running formulas are tables calc you would need to use window_percentile - the table version of pwercentils

           

           

          in table for it returns this

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: RUNNING_AVG in LOD
            Shinichiro Murakami

            HI Vladislav

             

            I need to confirm one thing.

            Your avg have multiple layers.

             

            1. Per [ID, time, Date]  within [ID x Date]

            2  Per [ID x Date] within [Location x Date]

            3  running avg for [Location x Date]

             

            Question is your percentile calc is supposed to be calculated at level of 1 or 2?

             

            Thanks,

            Shin

            • 3. Re: RUNNING_AVG in LOD
              Vladislav Zaimov

              Sorry, I am not able to answer to your question. I will try to explain it another way. I will simplify it.

               

              The resulting data represented as table have to be like this:

               

              To calculate the 25th percentile for Location 1 :

              for day 2018-01-01  - from source table I take the data only for 2018-01-01 and calculate average Amount for each ID so I have one value per ID. And then calculate 25th percentile of all IDs. The result is one number which I write in the resulting table next to 2018-01-01

              for day 2018-01-02  - from source table I take the data from 2018-01-01 to 2018-01-02  and do the above calculations then write the result next to 2018-01-02

              for day 2018-01-03  - from source table I take the data from 2018-01-01 to 2018-01-03  and do the above calculations then write the result next to 2018-01-03

              ....

              for day 2018-04-17  - from source table I take the data from 2018-01-01 to 2018-04-17  and do the above calculations then write the result next to 2018-04-17

               

              Maybe it will be easier to calculate it in the database using a loop instead of using Tableau.