2 Replies Latest reply on Jun 9, 2018 2:10 PM by Elia Berardo

    Running Calculation and divide into categories

    Elia Berardo

      Hello precious community,

       

      I need your Help today.

       

      I have the total amount of new rgesitration cars from 2008 until 2017 in EU.

      Each year  has a column with the toal amount.

       

      on the base of this number I want o calculate the amount of used cars. The logic is: the amount of new cars in 2008, will be in 2009 the total amount of the 1 year old cars, 2 years old in 2010 and 3 years old in 2011..and so on.

      Meanwhile, the new registration car in 2009, will be 1 year old car in 2010, 2 years old in 2011, and so on unitl 2017.

      I am struggling to find out a calculation but I cannot find a solution on this issue.

       

      Do you maybe have an idea?

       

      Thank you a lot!

       

      Elia

        • 1. Re: Running Calculation and divide into categories
          Jeevan Krishna

          Hi Elia,

           

          Thanks for attaching the data, very helpful in understanding the basic premise.The data shown is only the new registrations made in each year. You want to understand the total used cars in each year, which will include the new car counts from previous year too.

           

          You need to use running totals and achieve this.

           

          Before going there, the structure of your data is not convinient for the same task.So, we pivot this data for ease of calculation.

           

           

          Preparation Steps

          • In the data connection window, select (shift click) all year number columns.
          • Right click shows the options as shown above.
            Click Pivot.
          • You can rename the resultant Pivot Field Names and Pivot Field Values into something relevant such as Year and New Cars.

           

          Now this shows exactly what you see in the excel.

           

          Now change your aggregation to a running_sum calculation and you can see the overall car count on each year.

           

           

          Hope this helps.

          • 2. Re: Running Calculation and divide into categories
            Elia Berardo

            Thank you Eevan for your quickly reply.

            This is helpful,but my issue is a bit more complicated than that.

             

            Besides the running total,I want to create different groups of cars age (ex.1 years old,2,3, up to 4 years old).so that the end result should show me pro each year,not only the new car,but also (and most important)the number of used cars splitted into the 4 categories(1 year,2,3and 4 years).

             

            This looks really challenging to me and I don't know how to go further...

             

            Ottieni Outlook per iOS<https://aka.ms/o0ukef>