9 Replies Latest reply on Feb 15, 2017 8:50 AM by Yuriy Fal

# Calculate Running Total and layout

How do i calculate the running total for the SA row for the current year 2017?

Also is it possible to show SA row only for current year ie 2017 and not for any prior year period.

And would it be possible to show the layout as -

Oct   Nov  Dec  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep

CYTE

PTE

Current Year SA

Then show a line graph for the above table as well.

• ###### 1. Re: Calculate Running Total and layout

Any pointers on this would be appreciated

• ###### 2. Re: Calculate Running Total and layout

the first thing I saw on a quick look is that the date fields for this data are strings rather than dates.  I'd fix that as a start however he data in my testing there is no data for most months in 2017 so it appears the values that exist for any of the measures  are bucketed together in October of 2017 rather than in individual months.  This will create data issue to try and populate data for months where no data exists.  I started playing around with the data to get a handle on what was going on in the attached updated version of your workbook but I do not have a solution for dividing up the bucket of 2017 data captured over 12 months..  that said, you could divide the total /12 and then just display a single value for monthly values in 2017 - see sheet 4 for something like this as an example I have done setup 2 of the measures.

Then you could create a dashboard showing the monthly values for 2017  in a  dashboard with your 2016 values.

It's a start and I hope it gives you some ideas.

Patrick

ps - thank you for putting the version number in the workbook name.

• ###### 3. Re: Calculate Running Total and layout

Thanks Patrick for your time on this.

But the whole point is that allocation is done for a year. So i wont have the break down my months and that is what I want to do is split it monthly.

current allocation/12 and cumulative for rest of the months untill end of fiscal year from oct to sep.

• ###### 4. Re: Calculate Running Total and layout

Hi Dipti,

Something like this?

Yours,

Yuri

• ###### 5. Re: Calculate Running Total and layout

Hi Yuriy,

This is what I have been trying to do and breaking my head doing it..

Thank you so much for your help. I will now try to make it dynamic based on the Year filter.

Havent used tableau to perform such calculations so having a little bit of a hiccup getting it working the right way. But this gives me insight into the calc function out there.

U R THE BEST thank you!

• ###### 6. Re: Calculate Running Total and layout

Hi Yuriy Fal,

The workbook you posted is wat I am trying to do. But when I create a new worksheet and put in the selected measures the Allocation value just repeats and does not show up like yours as in YURIY sheet.

Can you please let me know what am i missing here and how can I get the allocation value split accros month cumumative.

• ###### 7. Re: Calculate Running Total and layout

Hi Dipti,

The calculation used here ( [YF : FSIS Straight Alloc] )

is a Nested Table Calculation, it contains a reference in the formula

to another ("inner") Table Calc, namely [YF : FSIS Straight Alloc Feeder].

For Nested Table Calcs not only the formula, but the proper

Addressing of both inner and outer calcs are important as well.

So for the correct results both inner and outer calculations

should be Addressing along (Compute using) --> Month.

That's how it set on my worksheet. To check, please,

open Edit Table Calculation dialog and look at the

Nested Calculations for the inner & outer calcs

and Compute using for the proper settings.

Hope it helps understanding.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 8. Re: Calculate Running Total and layout

Hi Yuriy Fal,

Oh yes its the compute using Month that made it work.. i get that now.

Thank you so much for your help.

This actually helped me in solving another calc that I was working on. Thanks a ton.

• ###### 9. Re: Calculate Running Total and layout

Dipti, you're welcome.