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,560 points
3,068 views

1 answer to this question.

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,240 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 9, 2018 in Tableau by ffdfd
• 5,550 points
4,764 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
15,296 views
0 votes
1 answer

How to fetch the previous value in a column or row?

Hi Sindhu, You can use LOOKUP() or PREVIOUS_VALUE() in ...READ MORE

answered May 22, 2019 in Tableau by Cherukuri
• 33,030 points
16,992 views
0 votes
1 answer

How to replace the count(field) which is missing in data with zero in tableau?

Use calculated field to create a dummy ...READ MORE

answered Dec 21, 2019 in Tableau by sindhu
2,004 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 6, 2018 in Tableau by Atul
• 10,240 points
5,607 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,560 points
17,324 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, 2019 in Tableau by Cherukuri
• 33,030 points
2,903 views
+2 votes
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,240 points
9,832 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,240 points
11,664 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP