how to get the count of non zero values in a row

0 votes

Sample Data:

day       employee    expense_reason        expense
2/24/2018   abc   car mileage               $5 
2/24/2018   xyz   car mileage               $5 
2/24/2018   xyz   car rent                  $8 
2/24/2018   xyz   car rent                  $9 

Find all the occasions where single employees have claimed both the expenses on the same day.

I have created the following view in Tableau using day and employees as rows and expense and the reason as column and finally sum(number of rows) on the text of the markscard.

What I am getting is the grand total using analysis>>totals>>show row totals instead of getting row totals, Can anyone help me to get the count of non zero values in a row

                  expense_reason        
day        employee   car mileage   car rent    grand total
2/24/2018   abc         1                         1
2/24/2018   xyz         1           2             3

update 1

I tried this but it is not giving output as I wantenter image description here

Apr 4, 2018 in Tableau by xyz
• 1,550 points
69 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

In first part you should solve the No-Data part since there is no data for the combo abc-rent.

In order to it you can use the lookup function, and force a fake lookup using 0 as parameter.

LOOKUP(SUM([Something]),0)

Then you can use ZN function and force a zero for a null value, which is different from No Data.

That being said, you should be able to get something like this:

enter image description here

Create this calculated field:

zn(LOOKUP(max({ FIXED [day],[employee],[reason] : COUNT([reason])}),0))

EDIT

If you want to have grand totals, i think this should be the better solution:

enter image description here

And the Calculated Field is a pure Count nested in a lookup function as said before:

zn(LOOKUP(COUNT([expense]),0))

answered Apr 4, 2018 by Atul
• 10,170 points

Related Questions In Tableau

0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 5,470 points
64 views
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
554 views
0 votes
2 answers

How can I add text in the centre of a Donut Chart ?

Hi, You can read this for creating a donut ...READ MORE

answered Apr 4 in Tableau by CHERUKURI
• 12,540 points
285 views
0 votes
1 answer

How to return the string between 5th and 6th Spaces in a String

In Tableau 9 you can use regular ...READ MORE

answered Jun 26, 2018 in Tableau by Atul
• 5,470 points
26 views
0 votes
1 answer

How can I count the aggregate function in Tableau

Create two calculated fields one for 100's ...READ MORE

answered Apr 5, 2018 in Tableau by Atul
• 10,170 points
917 views
0 votes
1 answer

How to use LOD to count distinct number of records based on some condition?

From the desired output it seems that ...READ MORE

answered Apr 9, 2018 in Tableau by xyz
• 1,550 points
1,851 views
0 votes
1 answer

How to aggregate and count the occurrence of TRUE and FALSE?

Hi, you can do any of below ...READ MORE

answered Mar 12 in Tableau by CHERUKURI
• 12,540 points
47 views
+1 vote
1 answer
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,170 points
898 views
0 votes
1 answer

How to count occurrence of value and percentage of a subset in tableau public?

Although it sounds like a fairly easy ...READ MORE

answered Jun 5, 2018 in Tableau by Atul
• 10,170 points
1,335 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.