12 Replies Latest reply on Jul 20, 2018 7:36 PM by Ajay Kulkarni

# how to carry a difference from one pane to other

Hello ,

This is my data set

orderdatetimeperiodcount
7/11/18Before100
7/11/18After40
7/12/18Before30
7/12/18After50
7/13/18Before100
7/13/18After102
7/14/18Before100

If you look at the data for 7/11, the BEFORE is 100 and AFTER is 70, meaning a difference of 30.

I want to build a report that shows three bars for every date

Bar 1 = BeforeCount

Bar 2 = AfterCount

Bar 3 = Backlog until previous date

Ex - for 07/11

Bar 1 = 100

Bar 2 = 40

Bar 3  = 0

Ex - for 07/12

Bar 1 = 30

Bar 2 = 50

Bar 3  = 60 (Subtract Bar1 from Bar 2 for the previous period ie 100 -40) and add Bar 3 of previous period ie 0

Ex - for 07/13

Bar 1 = 100

Bar 2 = 102

Bar 3  = 40 (Subtract Bar1 from Bar 2 for the previous period ie  30 -50) and add Bar 3 of previous period ie 60

effectively Bar 3 is backlog

How can i achieve this in tableau?

• ###### 1. Re: how to carry a difference from one pane to other

Hi, Ajay

Is it something like this?

If yes, please find attached workbook and screenshot of calculation fields I created.

Hope this helps

ZZ

• ###### 2. Re: how to carry a difference from one pane to other

It is working. I am not able to understand , how ZN and LOOKUP works.

Can you give some explanation please?

• ###### 3. Re: how to carry a difference from one pane to other

Hi, Ajay

Lookup is trying to get the value from previous date (-1 means previous value, 0 means itself, and 1 means next value), and if there is none, then ZN() converts null to 0 so that it can be part of the calculation. Hope this make sense.

ZZ

• ###### 4. Re: how to carry a difference from one pane to other

FOR 07/11 the BAR 3 should be zero as it is the starting period

FOR 07/12 the BAR 3 should 30 (Previous 100 - 70)

FOR 07/13 the BAR 3 should 10 (Previous Backlog of 10 + 30 -50)

• ###### 5. Re: how to carry a difference from one pane to other

Guys looking for help here...

• ###### 6. Re: how to carry a difference from one pane to other

Hi, Ajay

Your sample doesn't clear and I am confused.

FOR 07/11 the BAR 3 should be zero as it is the starting period

ZZ: can do

FOR 07/12 the BAR 3 should 30 (Previous 100 - 70)

ZZ: where is 100 come from and where is 70 come from?

FOR 07/13 the BAR 3 should 10 (Previous Backlog of 10 + 30 -50)

ZZ: where is 10 come from, and 10+30-50 = -10, not 10, isn't it?

ZZ

• ###### 7. Re: how to carry a difference from one pane to other

Sorry for the confusion

Lets assume Before = number of apples to sell in that week,

After = apples sold in that week.

Week 1 - Before = 100, After = 40 ( i was supposed to sell 100 apples but sold only 40, so backlog carried into next week was 60) (Going into Week 2 i had a backlog of 60)

Week 2 - Before =  30, After = 50 ( i was supposed to sell 30 and backlog of 60, and i sold 50, meaning backlog carried into week 3 is 40)

Week 3 - Before = 100, After = 102 ( i was supposed to sell 100 and backlog of 40 from previous week and i sold  102, so the back log carried into week 4 = 38)

Week 4 - Before = 50, After = 10 ( this is current week, backlog from previous week is 38)

Bar 1 - Before, Bar 2 = After, Bar 3 = CummulativeBackloguntilpreviousweek

For week1 the Bar 3 = 0

For week2 the Bar3 =60

For week3 the Bar3 =40

For week4 the Bar3 =38

• ###### 8. Re: how to carry a difference from one pane to other

Hi, Ajay

See my updated workbook as well as below screenshot

Hope this helps

ZZ

• ###### 9. Re: how to carry a difference from one pane to other

can you paste the calculation behind Bar 3. I dont have the current version of tableau yet and so unable to open your workbook

• ###### 10. Re: how to carry a difference from one pane to other

Hi, Ajay

the calculaation for bar 3 is

running_sum([before]) - running_sum([after])

ZZ

• ###### 11. Re: how to carry a difference from one pane to other

But it applies to the first date as well. I want the bar 3 to be ZERO in Group 1

How did you bring 0 for 11/07?

• ###### 12. Re: how to carry a difference from one pane to other

I figured out

ZN(LOOKUP(running_sum([Before]),-1)) -

ZN(LOOKUP(running_sum([After]),-1))