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

# Running Total issue

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

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

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

Here is a  10.5 version

• ###### 4. Re: Running Total issue

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...

Grace

• ###### 5. Re: Running Total issue

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

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

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