7 Replies Latest reply on Apr 26, 2018 7:51 AM by Shinichiro Murakami

# Waterfall chart with filter on Date

I am new to tableau. I am working on a project where I need to create a waterfall. I have the data(sample) which is attached in the excel sheet below.

There 3 columns 'QuarterEndDate', 'Name' and 'Value'.

QuarterEndDate has to be a filter. Now for example someone click 2 quarters - 30-09-2017 and 31-12-2017, in the final stage I want only the beginning balance of 30-09-2017 and the aggregate of all chargeoff, closed, balance_x, new enrollment, and the ending balance of 31-12-2017.

so this is what i want in bar chart

Bar 1 - Beginning balance of 30-09-2017 ,

Bar 2 - sum(all closed)

Bar 3 - sum(all charged off)

Bar 4 - sum(balance_x)

Bar 5 - Ending balance of 31-12-2017

NOTE:  30-9-2017 and 31-12-2017 is not fixed, the quarters are to be selected based on the filter and could be anything either one quarter or two or all.

Issue is when I draw the bar chart - I get the aggregate of beginning balance of all the quarters, but I don't want that.

This basically shows where we started in the quarter say 100 accounts, 10 charged off so -10, 20 closed so -20, new enrollment say 30 and balance_x is say 40, ending is 100-10-20+30+40 = 140 (the ending balance for latest quarter selected)

This has been highlighted in the picture attached below.

Let me know if you have any trouble understanding this. Thanks in advance!

Thanks

• ###### 1. Re: Waterfall chart with filter on Date

Hi Yasar,

Yes, really difficult to understand the logic.

Individual month, the calculation matched, but across month from end to begin....

Filtering multiple dates, logic never work...

Single month works

Or consolidating all dates also works.

Thanks,

Shin

• ###### 2. Re: Waterfall chart with filter on Date

Forgive me if I am not able to explain it correctly, let me put this in other way

Scenario 1:

If a person selects 2 quarters from the filter - Q3'2017 and Q4'2017...I want these numbers on the bar

bar 1 = beginning balance - 120,000 (beginning balance of 30-09-2017)

bar 2 = -18044 (sum of charged_off for 30-09-2017 & 31-12-2017)

bar 3  = -24555 (sum of closed for 30-09-2017 & 31-12-2017 )

bar 4

bar 5

bar 6 = ending balance - 1944 (ending balance for 31-12-2017)

Challenge is with bar 1 and bar 6. There has be some filter which picks only the beginning balance from the earliest quarter and some other filter that picks only the ending balance from the latest quarter. For rest - chargeoff. closed, new enrollment, we are already getting the aggregate values.

• ###### 3. Re: Waterfall chart with filter on Date

Yasar

No progress to my understandings.

[beginning balance + each category sum] never matches to ending balance if across the quarter.

How to handle the gap?  or I don't understand that gap.

Shin

• ###### 4. Re: Waterfall chart with filter on Date

Shin, actually these are sample numbers which I put randomly, in actual data, it always matches..

• ###### 5. Re: Waterfall chart with filter on Date

Hi Yasar

To be honest, that's pretty bad, confusing example... anyways..

I modified excel file to make it work.

LOD

Top 15 Tableau LOD Expressions (Practical Examples)

Context filter

Tableau's Order of Operations

Thanks,

Shin

• ###### 6. Re: Waterfall chart with filter on Date

That's awesome. It worked and saved my life. I could not have done on my own. Thank you so much Shin.

Thanks,

Yasar

• ###### 7. Re: Waterfall chart with filter on Date

Hi Yasar

You are welcome and to close the thread, could you mark my answer as correct.

Not from inbox view but from original post.

Thanks,

Shin