How to create a single calculated field from multiple column filtering?

0 votes

What I want?

I have 3 columns (2 dimensions and 1 measure) and I want to use the measure column based on some of the string occurrences from the dimensions columns.

Sample columns: 'Event', 'Screen' and 'Time'

Sample Dataset: (NOTE: There are many other values along with these, but I require only the ones in the lower cases i.e., NULLs and NAVIGATIONs are to be excluded)

enter image description here

I want a single calculated field with three steps that yields as below:

(2*count of "name submitted" occurances in Event) - (AVG time of corresponding "name submitted" (from Event) * count of "name submitted" occurances in Event)
+

(2*count of "address added" occurances in Event) - (AVG time of corresponding "Add address" (from screen) * count of "address added" occurances in Event)
+

(2*count of "order created" occurances in Event) - (AVG time of corresponding sum of "orders"+"order detail"+"order confirmation" (from screen) * count of "order created" occurances in Event)

My approach:

I have dragged Event and Screen dimensions to the filter pane and selected all the values including NAVIGATION AND NULL (as these fields correspond to the time that I need in the calculation) and it didn't quite work!

I have also created PARAMETERS for Event and Screen for each of the three steps of the calculation (above) and it didn't work either!

So, what would be a best way to achieve the above calculation?

Any help is much appreciated.

Jul 6, 2018 in Tableau by Atul
• 10,240 points
38 views

1 answer to this question.

0 votes

Try with this calculated field:

(
    (2 * SUM(IIF([Event] = 'Name submitted',1,NULL)))
    - 
    (
        AVG(IIF([Event] = 'Name submitted',[Time],NULL))
        * SUM(IIF([Event] = 'Name submitted',1,NULL))
    )
)

+

(
    (2 * SUM(IIF([Event] = 'Address added',1,NULL)))
    -
    (
        AVG(IIF([Screen] = 'Add address',[Time],NULL))
        * SUM(IIF([Event] = 'Address added',1,NULL))
    )
)

+

(
    (2 * SUM(IIF([Event] = 'order created',1,NULL)))
    -
    (
        AVG(IIF([Screen] = 'orders'
                OR [Screen] = 'order detail'
                OR [Screen] = 'order confirmation'
            ,[Time],NULL))
        * SUM(IIF([Event] = 'order created',1,NULL))
    )
)
answered Jul 6, 2018 by Atul
• 5,530 points

Related Questions In Tableau

0 votes
1 answer

How to get multiple Sets of Measure Names with different Filters on a single sheet in Tableau?

The easiest solution to your query is: 1) ...READ MORE

answered Aug 23, 2018 in Tableau by Naruto
• 710 points
1,425 views
0 votes
1 answer

Create global filter from single/multiple data source to multiple data sources on dashboard.

Hi Sindhu, You can use filters from worksheets in ...READ MORE

answered Mar 11 in Tableau by Cherukuri
• 25,900 points
57 views
0 votes
1 answer

How to perform a calculation using one variable with an aggregation in calculated field?

Hi, You can use the calculative field and ...READ MORE

answered May 20 in Tableau by anonymous
• 25,900 points
30 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
1,815 views
0 votes
1 answer

How to determine average per event by year?

Hi Roshan, Create a new calculated field (use ...READ MORE

answered Sep 25, 2018 in Tableau by Machdata
• 340 points
54 views
0 votes
0 answers

Calculating count average from averages

I've a calculated field in Tableau which ...READ MORE

May 28, 2018 in Tableau by Mahima Choudhary
23 views
0 votes
1 answer
0 votes
1 answer

How to Split a Measure in Tableau?

Have you tried using an IF statement: IF ...READ MORE

answered Aug 14, 2018 in Tableau by AwesomeSauce
• 860 points
93 views
0 votes
1 answer

How can I use Tableau to create a heatmap?

Step 1: Create an Excel Spreadsheet with ...READ MORE

answered Apr 2, 2018 in Tableau by Atul
• 5,530 points
616 views
0 votes
1 answer

How can I create a second sheet which is filtered to 8 days ago,

Following are the steps you can follow: Create ...READ MORE

answered Jul 11, 2018 in Tableau by Atul
• 5,530 points
38 views