7 Replies Latest reply on Apr 22, 2019 11:48 AM by Grace Raj

    Running Total issue

    Grace Raj

      Hello Everyone,

       

      Here , I am trying to implement running total trend on the bar ,

       

      but I am getting circular reference error

       

      Column B: count of Approval Date for each month

      Column D: Count of Closed Date for each month

      Calculation for Column E: Column C-Column D

       

      I am trying to populate below running total calculation which is in Column C in the  trend on the bar chart

      Calculation for Column C : Column A +Previous Month Balance which is from Column E

      Please let me know how to implement this logic in tableau..

       

      Thanks

      Grace

        • 1. Re: Running Total issue
          Michel Caissie

          Grace,

           

          Since you have 2 date dimensions and you want to display measures on a date axis, and the measures logic is based on both date dimensions,  I suggest that you first start by pivoting your 2 date dimensions in a single one.

          So you would get  a dimension Pivot Field Name  with values of  'Approved Date' and 'Closed Date' , and a dimension  Pivot Field Values with the dates.

          And since you want to group by Month, you could create a Date(Month) dimension with

          DATETRUNC('month', [Pivot Field Values] )

           

          From this, you can get the Open(Monthly)

          { FIXED MONTH([Date(Month)]): COUNTD(if [Pivot Field Names] = 'Approved Date' then [Id] end)}

           

          and the Closed(Monthly)

          { FIXED MONTH([Date(Month)]): COUNTD(if [Pivot Field Names] = 'Closed Date' then [Id] end)}

          check on ValidateSheet1

           

           

          Now to get your balance without getting the circular reference error, you can use the PREVIOUS_VALUE()  function.

          So the Balance would become

          if first() = 0

          then MIN( [Open (monthly)] ) - MIN( [Closed (monthly)] )

          else MIN( [Open (monthly)] ) + PREVIOUS_VALUE(0) - MIN( [Closed (monthly)])

          end

          So for the first row, simply compute the difference between the Open and the Closed

          and for the other rows, add the result of this calculation from the previous row.

           

          see ValidateSheet2.

          note: you get negative values for the balance, but if you look in validateSheet1, you have more Closed than you have Open.

          Maybe you had Opens from 2017 that got closed in 2018 ???

           

          Michel

          • 2. Re: Running Total issue
            Grace Raj

            Hi Michel,

             

            Would you please upload the tableau package once again... I am not able to open it..it says ,The load was not able to complete successfully....

             

             

            Thanks

            Grace

            • 3. Re: Running Total issue
              Michel Caissie

              Here is a  10.5 version

              • 4. Re: Running Total issue
                Grace Raj

                Hi Michel Caissie

                 

                When I try to pivot the Approved Date and Closed Date in Data Source area... all the other calculation became invalid/error because of the Approval Date and Closed Date field which is not valid after changing into pivot..

                How do I solve this issue...

                 

                Thanks for your help

                Grace

                • 5. Re: Running Total issue
                  Michel Caissie

                  It's sure that pivoting changes the dataset and you will have to adjust some calculations.

                  If you have a lot of worksheets already using the dataset without pivot, you may want to create  a separate datasource connection for this worksheet on which you will apply the pivot.

                   

                  If you simply want to adjust your previous calculations, then you have to study your new dataset after pivot.

                  Each Id now have 2 rows instead of one, so be carefull before counting dimensions to make sure you don't count duplicates.

                   

                  If you need to do row level calculations between the Approved and Closed Date,  you can fix those values  per  ID.

                  Closed Date would become

                  {FIXED [Id]: MIN(if [Pivot Field Names] = 'Closed Date' then [Pivot Field Values] end)}

                   

                  and Approved Date would become

                  {FIXED [Id]: MIN(if [Pivot Field Names] = 'Approved Date' then [Pivot Field Values] end)}

                   

                  Again, be carefull if you COUNT Dates , because this will give you a copy of each date on both row of the ID.

                  You may want to COUNTD(Id)

                  • 6. Re: Running Total issue
                    Grace Raj

                    Hi,

                     

                    Now I connected to sql server database, I am not seeing the option for pivoting the columns... in Data source area in Tableau.. How do I achieve the running total logic..

                     

                    Thanks

                    Grace

                    • 7. Re: Running Total issue
                      Grace Raj

                      Hello evreyone,

                       

                      but I am getting circular reference error

                       

                      Can someone give me the solution without pivoting the fields because the data source is sql server... not excel...How to get the Balance measure without the circular reference error,

                       

                      Column B: count of Approval Date for each month

                      Column D: Count of Closed Date for each month

                      Calculation for Column E: Column C-Column D

                       

                      I am trying to populate below running total calculation which is in Column C in the  trend on the bar chart

                      Calculation for Column C : Column A +Previous Month Balance which is from Column E

                      Please let me know how to implement this logic in tableau..

                       

                      Thanks

                      Grace