7 Replies Latest reply on Jun 23, 2016 11:14 AM by Ivan Young

    Calculating a Date at 50% running total

    Gorka Garcia Tapia

      Hello,

       

      I have:

      Order dates

      Orders

       

      I have the running total function for ORDERS as a % of the total.

      I can therefore identify the 50% mark, visually I can see what date this refers to i.e. I can see the 50% point in tableau select it and see what date it refers to.

       

      However I need to create a calculated field that will identify the specific date.

      The reason I need this is that I will be doing this process with different data series, and my idea would later be to find the days difference between one date and the other.

       

      can anyone help with this?

       

      Gorka

        • 1. Re: Calculating a Date at 50% running total
          Ivan Young

          Hi Gorka,

          It's possibly to create a filter to find the row where running % of total switched from below 50 to 50 or above, a running of % of total will rarely return exactly 50%.  What sort of date difference are you trying to calculate?

           

          Regards,
          Ivan

          • 2. Re: Calculating a Date at 50% running total
            Gorka Garcia Tapia

            Hi Ivan,

             

            if I can set a range of % that would also work for ny purpose i.e. 49 to 50% as long as I get 1 date.

             

            I am basically going to have to measure the amount of days that there are between the 50% running total of 1 data set and the same of another (different item ordered).

             

            if you could help, thqt would be great,

             

             

             

             

            Sent from my Samsung Galaxy smartphone.

            • 3. Re: Calculating a Date at 50% running total
              Ivan Young

              Hi Gorka,

              In order to isolate the row where the running sum first reached 50% we will build three calculated fields, one for running sum, another that is a lookup of the running sum in the previous row and lastly our filter.

               

              Running Sum:  RUNNING_SUM(SUM([Sales]) / TOTAL(SUM([Sales]))) -

               

              Running Sum Previous:  LOOKUP(RUNNING_SUM(SUM([Sales]) / TOTAL(SUM([Sales]))),-1) -

               

              Filter-Date 50% Achieved:  [Running Sum] >= .5 and  [Running Sum Previous] <.5  Drag this field to filters and set to True.

               

              You will need to set the correct compute for these table calcs.  I'm computing  using Order Date in my example which calculates the day 50% or greater was achieved.

               

              Let me know if you have any questions.


              Regards,
              Ivan

               

              1 of 1 people found this helpful
              • 4. Re: Calculating a Date at 50% running total
                Gorka Garcia Tapia

                Hi Ivan,

                This worked perfectly, I am now able to identify the date at which the order was 50%.

                however, how can I do the final step? i.e.

                 

                Item 1: order date at 50% =  3rd Sept 2013

                Item 2: order date at 50% = 10th sept 2013

                 

                date difference between the 2 = 7 days

                 

                thank you once again!

                • 5. Re: Calculating a Date at 50% running total
                  Ivan Young

                  Hi Gorka,

                  I played around with lookup for a while but I'm not sure how to target the correct row.  It might be possible but I couldn't figure it out.  If we could lookup over the Date 50% Achieved measure it would be easy but that doesn't seem to be possible.  If you could tag these records in your datasource and create a flag for the date 50% achieved records it would make the lookup a lot easier.

                   

                  Regards,

                  Ivan

                   

                  • 6. Re: Calculating a Date at 50% running total
                    Gorka Garcia Tapia

                    Hi Ivan,

                    thank you, Ill tag the records in a new field.

                    if I do this, how could I then do the lookuo?

                    regards.

                     

                     

                     

                     

                    Sent from my Samsung Galaxy smartphone.

                    • 7. Re: Calculating a Date at 50% running total
                      Ivan Young

                      Hi Gorka,

                      If you identify the records in the datasource and create a dimension it's super easy.  You just filter by the tagged value, Order by Min(date) asc, then lookup offsetting to FIRST().

                       

                      First:  LOOKUP(MIN([Order Date]),FIRST()) - looks up the first row.

                       

                      Days After:  DATEDIFF('day', [First], ATTR([Order Date]))  - This is the number of days after the first segment reached it's midpoint.

                       

                      Output

                       

                      Raw Data

                       

                      Regards,

                      Ivan