How to get the difference between 2 groups in a field

[HarveyB-B]

So i have created a waterfall chart which shows the flow of cases and what statuses they have been in.

Current groups in the field “casestatus”:
Bar 1 = Initial Enquiry = 20,000
Bar 2 = AOB = 15,000
Bar 3 = Pending = 10,000
Bar 4 = Live = 2,500

I want to take this data, and do the following:
Bar 1 = 20,000 (“total” or original amount)
Bar 2 = -5,000 (Bar 2 - Bar 1)
Bar 3 = -5,000 (Bar 3 - Bar 2)
Bar 4 = -7,500 (Bar 4 - Bar 3)

So what this does is shows me the difference (number of drop-off) between each case status, which in a waterfall would show this decline in cases. Is this possible to do?

[HarveyB-B]

If someone can help with the below, i think i can solve it:

This is the distinct count of applicantid, in initial enquiry (which is within the group casestatus). How can i get this via a calculated field so its a standalone value, which relies on applicantid / casestatus.

So the KPI would state “20,000”

[ErikG]

Do you mean something like

[HarveyB-B]

Yes, this is what i am looking for. I do have more fields i need to include but i can adjust based on how you got to this point.

To tidy it up, is there any way to remove ‘Total’ ?

[ErikG]

Waterfall

Not sure you can remove. But you could relable it.

[HarveyB-B]

Bit stuck on the Arena. What is the ‘Bar’ field? I’ve tried to replicate the calculated field you use to get the number on it’s own, but not sure what to replace ‘Bar’ with.

Edit:

The fields i am using is:

casestatus - this is what holds initial enquiry, AOB, Pending etc.
applicantid - these show all the caseID’s for all cases. I am using this to get the distinct_count of applicants in each status

So do i need to use these to replace value and bar? If so how, since i have tried and errors appearing

[HarveyB-B]

How do i get the link to an arena analysis to share? I have replicated the fields from my actual dataset into an arena which for me will be more useful for you to use

[ErikG]

Within Arena is a share icon (on the left side).

There you can “publish” and copy the link.

[HarveyB-B]

Ok thank you.

If you check this one out and try to replicate the calculated field which gets the difference using the fields i am using.
waterfall

Then i can copy over to my real analysis, where it will hopefully get the difference between the actual numbers.

-Thanks

[ErikG]

Hi @HarveyB-B

have a look at waterfall2
i just created the first steps.

BR

[HarveyB-B]

One thing i just realised is that the arena i made i took the underlying data from a graph. So i don’t actually have the field applicantid (distinct count), i just took applicantid and set to a distinct count on the visual i copied the data from.

I can make this field, but then when i do it then breaks the other calculated field, since applicantid (distinct_count) is now an aggregated field, opposed to a field already existing.

I guess the new question is:
How can i use these fields you created but using applicantid(distinct_count) which is now an aggregated field?

EDIT: what i did was go into the data, and add applicantid(distinct_count) as a field in the backend, but even when using your calculated fields i still get the same issue?

[HarveyB-B]

[ErikG]

Hi @HarveyB-B
what about and distinct_countif instead?

distinct_countIf({applicantid},casestatus="Initial Enquiry")

BR