10 Replies Latest reply on Oct 16, 2015 12:10 AM by Shinichiro Murakami

# Question regarding running total

I want to make table to show inventory calculated through supply and demand.

It's easy to make this through excel, but I don't have good idea to do it in Tableau table calculation.

I attached both Excel and Tableau picture and Excel picture/file for you understand my needs.

Thanks,

Shin

• ###### 1. Re: Question regarding running total

If you just need to see Inventory then use this calc

WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0)

• ###### 2. Re: Question regarding running total

Thank you, Daniel

But the problem is I don't want to show Inventory with "less than 0" at any time.

Inventory should be always max(0, calculated result).

And Period 2's inventory = Max ( 0 ,Period 1's inventory + Period 2's Supply - Period 2's Demand )

Hope I explain what I need.

Thanks,

Shin

• ###### 3. Re: Question regarding running total

So if inventory drops below 0 then it should only show 0 else show actual positive inventory?   Something like this instead?

IF WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0) < 0 THEN 0

ELSE WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0)

END

• ###### 4. Re: Question regarding running total

I understand your approach, but that brings the situation of incorrect case in Excel.

Need to calc previous cell with current cell.

Thanks,

Shin

• ###### 5. Re: Question regarding running total

Okay....I think I covered all the bases but it's possible I missed something.  Take a look and just let me know if its good or needs more tweaking.

IF (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0)) > 0 THEN (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0))

ELSEIF (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0)) < 0 AND (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0)) < 0 THEN 0

ELSE (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0))

END

• ###### 6. Re: Question regarding running total

Daniel,

Thank you many times, but the last part of formula should be accumulated, and not from the left end. (Which is the error version)

Hope you are still not tired..

Thanks,

Shin

• ###### 7. Re: Question regarding running total

You do realize your values for period 11 on is different between what you have in Tableau and Excel, right?  Regardless, I did catch that last part where I needed to start over the count of inventory.  My eyes might be crossed at this point so I'll probably take a break and come back to see if this is right or needs another go round...

IF INDEX()=1 THEN (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0))

ELSEIF (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0)) > 0 AND (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0)) < 0 THEN (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0))

ELSEIF (WINDOW_SUM(SUM([Supply]),FIRST(),0) -WINDOW_SUM(SUM([Demand]),FIRST(),0)) < 0 AND (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0)) < 0 THEN 0

ELSEIF (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0)) > 0 AND ZN(LOOKUP(SUM([Supply]),-1) -LOOKUP(SUM([Demand]),-1)) < 0 THEN (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0))

ELSEIF  (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0)) > 0 AND ZN(LOOKUP(SUM([Supply]),-1) -LOOKUP(SUM([Demand]),-1)) > 0 THEN (LOOKUP(SUM([Supply]),0) -LOOKUP(SUM([Demand]),0)) + ZN(LOOKUP(SUM([Supply]),-1) -LOOKUP(SUM([Demand]),-1))

END

1 of 1 people found this helpful
• ###### 8. Re: Question regarding running total

Still not correct.

New formula only covers two period in a row.

Thanks,

Shin

• ###### 9. Re: Question regarding running total

Use

max(PREVIOUS_VALUE(0)+[Supply-Demand],0)

P.S : for last 3 periods the value you provided in excel sheet 6 does not match with your jpg file

• ###### 10. Re: Question regarding running total

Nasrin,

Thank you for the advise, and sorry for the confusion of screenshot.

Anyways, looks like it worked with below formula.

if index() =1 then max(0,sum([Supply])-sum([Demand])) ELSE max(0, previous_value(1)+sum([Supply])-sum([Demand])) END

Thank you very much for you help,

Shin