7 Replies Latest reply on Nov 18, 2015 1:09 AM by James Peart

Calc field with multiple conditions counted separately

Hi all,

I'm basically looking at the users' progress through a purchase journey to see drop out points.

The stages each user completes are stored as an array in the database

The journey is

A -> B - > C-> D -> E -> F              (each letter represents a stage where A is the start and F is completed purchase)

If we take 4 users for the example

user 1: A,B,C,D,E,F

user 2: A,B,C,D

user 3: A,B,C,D,E

user 4: A,B,

I want to count how many users complete each stage

A=4                          (IF journey contains A)

B=4                              (IF journey contains B)

C=3                                  (IF journey contains C) etc.

D=3

E=2

F=1

How would I create a single calculated field to do this?

[I'll probably be making a funnel chart from it]

PS if you can think of a better title for this question I'd like to change it because it's not very clear

Thanks,

James

• 1. Re: Calc field with multiple conditions counted separately

You could probably use a LoD calc:  {FIXED [Journey Dimension]:COUNTD([User Dimension])}

• 2. Re: Calc field with multiple conditions counted separately

Thanks David, that could help with part of the problem but the journey dimensions needs to have multiple 'contains' conditions to pull the stages out of an the array

• 3. Re: Calc field with multiple conditions counted separately

How does your data look like?

I have 2 solution, the second one being the easiest:

1) I have considered your data has 2 column :user and path

use the formulae to create [A]:

iif(len(TRIM( SPLIT( [Path], ",", 1 ) ))>0,1,0)

and create till [F]

use measure name in row and measure value in columns. Filter measure name to include only [A]-[F] calculated fields.

Change mark type to line.

2) Assumig you have excel source, split up path to form A-F

In tableau select these dimention and Pivot to get two columns. Use pivot field values in rows and bring count(User) to rows and also -1*count(User)

Make dual axis and synchronize!

1 of 1 people found this helpful
• 4. Re: Calc field with multiple conditions counted separately

It would help if you would post a packaged workbook with a sample data set.  If you need to anonymize your data:  Anonymize your Tableau Package Data for Sharing

• 5. Re: Calc field with multiple conditions counted separately

Hi both,

It's a postgreSQL database source.

Posting a packaged workbook won't be very helpful as there are several other conditions I need to have in place to create something matching the example - explaining the logic would be impossible out of context. The example has it boiled down to how to do just this part of it (it's the only part I'm stuck on).

Nasrin - your first example is roughly how I initially tried to do it but couldn't then move it towards a funnel diagram (or something similar) using measure values, to make it visually simple.

Thanks for your help, sorry if it isn't possible without a packaged workbook.

• 6. Re: Calc field with multiple conditions counted separately

Does using :

if contains([Path],'F') then 'F'

elseif contains([Path],'E') then 'E'

elseif contains([Path],'D') then 'D'

elseif contains([Path],'C') then 'C'

elseif contains([Path],'B') then 'B'

elseif contains([Path],'A') then 'A'

end

and then calculating running sum of countd help?

I am thinking the path points will be unique and acyclic.

Also you will have a lot of data points so that not one of the path points remains missing.

1 of 1 people found this helpful
• 7. Re: Calc field with multiple conditions counted separately

Running sum approach could work ... I hadn't though to do it that way

Thanks Nasrin I'll try that out