Creating a waterfall using a single field, and the groups as the stages

I have been set a little task. We have a field where it tell us what stage each case is at. So let’s say there are 5 stages, before it is considered “Complete”. At any stage they can be declined. How do i create a waterfall chart where it shows the fall-off of cases. So starting at stage 1 with 1000, stage 2 could be 750, and so on. So rather showing an increase OR decrease, it should only show a decrease.

So i guess it should look like this, but be stage 1, stage 2, stage 3 etc. And i should be able to set the order.

Edit: The waterfall chart should exclude ‘Decline’, since the difference in count of each stage will tell us how many were declined. Should go Total Cases > Stage 1 > Stage 2 > Stage 3 > Stage 4 > Stage 5

Hi @HarveyB-B
who is your dataset looking? Are the 5 stages one field?
Did you already build something?
The sort you could achiev with the “sort options”
Maybe it will help to setup a Arena sample, if possible.

So it’s basically stages of a credit card application process. We have a field named “currentStatus” which is what part of the stage they are at (group field)

The currentStatus shows a count for:
Initial Application
Background Check
BankDetails Check
Pending Acceptance

then final would be going onto receiving the card, so i guess it 6.

Currently looks like this:

However the order is a bit weird, but should be fixable if i do DESC.

I think i am able to it from this, just took a bit of playing around. The only thing i want to do is change the colours for each stage. I thought i can do that by changing what they are grouped by, but since i am using the field already, i can’t put the same one on the chart. So will remake the field as a calculated field, apply it and it should work.

The one problem i am facing is that i need to add the count of a bar to the previous

View screenshot below:


the far right should be total applications,
the one next to it should be stage 1 but it should include the count of stage 2/3/4/5/6,
the next is stage 2 but should include count of 3/4/5/6, and so on.

The reason is that all applications in stage 5 have gone through 1/2/3/4 so should be included in the count of these stages, however it’s only showing in stage 5 since thats how the dataset works.

I hope this makes sense?

Will create a fake dataset and create an arena for you to play around in.

For extra information, the stages at the bottom are taken from the CRM, so all current applications in that stage will be added to the count. I need to to be overall applications which have been through that stage instead.

Weirdly i can’t upload a csv or excel file to arena. If i attach here you may try?

Or if you want to create and import, i just entered this:

|Stage1|Stage2|Stage3|Stage4|Stage5|Stage6|Live Customers|
|1000 |750 | 250|125 |75 |2 5|15 |

The one problem i am facing is that i need to add the count of a bar to the previous

But stages are different fields or one field with different items?

They are in one field. So they field is showing all stages which the count - however this is current cases in this stage, rather than all stages which have been in this stage.

I think what i need is some what of a reverse running sum. So “Total applications” would be the count of all cases in every stage. Stage 1 would be the current count + count of all stages after. Stage 2 would be current count + count of all stages after, and so on.

Is it worth creating a calculated field for each stage, and then concat them at the end, or just use each field and put in the waterfall. I think this would be best approach, just need to create the calculated field to create this reverse running sum

The best way for me to describe it or state it is that every bar after a stage should equal that stage.

Hello @HarveyB-B, if you still need assistance with this question, please post a new topic in the community and link to this topic. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!