6 Replies Latest reply on Jul 5, 2016 3:44 AM by Sankarmagesh Rajan

# Lookup / Previous_Value / Table Calculations

Hello,

Fairly new to Tableau, so any help appreciated on this!!

So, here I am trying to achieve stock for week 28 on-wards.

Stock for week 28 is a calculation of Stock (from previous week) + Production - Forecast

In essence, the excel calculation of this:

Please note each of these come from different data sources.

So far I am able to calculate the first week (Week 28) using a lookup. However I was expecting this to continue to calculate across all weeks?

Thanks,
Joe

• ###### 1. Re: Lookup / Previous_Value / Table Calculations

hi Joe,

if ISNULL(sum([Stock])) then PREVIOUS_VALUE(sum([Stock]))+  lookup(SUM([Product]),-1)-LOOKUP(sum([Forcast]),-1 )

else sum([Stock])

END

Use   calc in view and change compute using week then get desired output.

Thanks

sankar

• ###### 2. Re: Lookup / Previous_Value / Table Calculations

Hi Joe

Here with my version - (I think this is how you want to calculate it...)

2 versions (depends if you need to fill the week 27 stock)

With (as above) =

IF INDEX() == 1 THEN SUM([Stock]) ELSE ( LOOKUP(RUNNING_SUM(SUM([Stock])),-1) + LOOKUP(RUNNING_SUM(SUM([Product])),-1) ) - LOOKUP(RUNNING_SUM(SUM([Forecast])),-1) END

Without =

( LOOKUP(RUNNING_SUM(SUM([Stock])),-1) + LOOKUP(RUNNING_SUM(SUM([Product])),-1) ) - LOOKUP(RUNNING_SUM(SUM([Forecast])),-1)

Enjoyable problem

v9.3.3. attached.

Cheers

Mark

1 of 1 people found this helpful
• ###### 3. Re: Lookup / Previous_Value / Table Calculations

Hi Sankar

Although week 27 is correct, the rest of the weeks just add increments of 10?

I'm no so sure, becuase Joe gave a clue here, as to his expectations...

Try in Excel (copy attached)

See what I mean?

Cheers

Mark

• ###### 4. Re: Lookup / Previous_Value / Table Calculations

Hi Mark,

I have edited my calc now in first reply. Pls try once.

if ISNULL(sum([Stock])) then PREVIOUS_VALUE(sum([Stock]))+  lookup(SUM([Product]),-1)-LOOKUP(sum([Forcast]),-1 )

else sum([Stock])

END

we both answered same question now with different way.

Mark rocks

Thanks

sankar

1 of 1 people found this helpful
• ###### 5. Re: Lookup / Previous_Value / Table Calculations

Thank you Sankar, that's now working as expected, although need to spell forecast correctly...

if ISNULL(sum([Stock])) then PREVIOUS_VALUE(sum([Stock]))+  lookup(SUM([Product]),-1)-LOOKUP(sum([Forecast]),-1 )

else sum([Stock])

END

Apologies, I didn't mean to pick on you - its nothing personal.

I just think it's important we (all contributors, me included) test solutions first...

Sankar rocks

• ###### 6. Re: Lookup / Previous_Value / Table Calculations

hi Mark,

Sure thanks